# DataInsight Pro — SQL Edition
# Partie 1 : Setup + Base de Données SQL Réelle

---

> **Bienvenue dans DataInsight Pro — SQL Edition.**
> Ce projet fil rouge utilise la base de données **Chinook** — une base de données
> réelle représentant un magasin de musique numérique (équivalent iTunes).
> Elle est maintenue sur GitHub et utilisée par Microsoft, Oracle et SQLite
> comme base de démonstration officielle.
>
> **Source officielle** : https://github.com/lerocha/chinook-database
> **Données** : 11 tables, ~10 000 lignes, vraies données musicales (artistes, albums,
> pistes, clients, factures, employés).

---

## Table des matières

1. [Contexte métier réel](#1-contexte-métier-réel)
2. [Objectifs pédagogiques](#2-objectifs-pédagogiques)
3. [Architecture du projet](#3-architecture-du-projet)
4. [Installation et setup](#4-installation-et-setup)
5. [La base de données Chinook — analyse complète](#5-la-base-de-données-chinook--analyse-complète)
6. [Code : `src/db_connection.py`](#6-code--srcdb_connectionpy)
7. [Code : `src/utils.py`](#7-code--srcutilspy)
8. [Code : `main.py` — point d'entrée](#8-code--mainpy--point-dentrée)
9. [Bonnes pratiques SQL et Python](#9-bonnes-pratiques-sql-et-python)
10. [Erreurs fréquentes](#10-erreurs-fréquentes)
11. [Exercices](#11-exercices)
12. [Corrigés](#12-corrigés)
13. [Récapitulatif](#13-récapitulatif)

---

## 1. Contexte métier réel

### L'entreprise : Chinook Music Store

**Chinook** est un distributeur de musique numérique fictif mais réaliste,
dont la base de données est utilisée comme référence industrielle depuis 2008.

**Problématique business** :
Tu es embauché comme Data Analyst Junior chez Chinook Music. Ton manager
te confie les missions suivantes sur les 8 semaines du projet :

|---------|-----------------------------------------------------------------|
| Semaine | Mission                                                         |
|---------|-----------------------------------------------------------------|
| 1-2     | Comprendre la base, établir les connexions, explorer les tables |
| 3-4     | Écrire des requêtes SQL pour répondre aux questions business    |
| 5-6     | Nettoyer les données et réaliser l'EDA                          |
| 7       | Construire des visualisations professionnelles                  |
| 8       | Produire un rapport final avec recommandations                  |
|---------|-----------------------------------------------------------------|

**Questions business réelles** :
- Quels pays génèrent le plus de revenus ?
- Quels genres musicaux se vendent le mieux ?
- Quels sont les agents de vente les plus performants ?
- Quelle est la valeur moyenne d'une facture par pays ?
- Quels clients sont les plus fidèles (LTV) ?

---

## 2. Objectifs pédagogiques

À la fin de cette Partie 1, tu sauras :

| Compétence | Niveau |
|-----------|--------|
| Installer SQLite et connecter Python | [OK] Acquis |
| Comprendre un schéma relationnel (PK, FK, 1:N) | [OK] Acquis |
| Lire et analyser la documentation d'une base | [OK] Acquis |
| Écrire les premiers `SELECT` SQL | [OK] Acquis |
| Structurer un projet Python professionnel | [OK] Acquis |
| Utiliser `sqlite3` et `pandas` ensemble | [OK] Acquis |

---

## 3. Architecture du projet

```
datainsight_sql/
│
├── data/
│   └── chinook.db              <- Base SQLite réelle (à télécharger)
│
├── notebooks/
│   └── exploration.ipynb       <- Exploration interactive Jupyter
│
├── src/
│   ├── __init__.py             <- Rend src/ un module Python
│   ├── db_connection.py        <- Gestion de la connexion SQLite
│   ├── queries.py              <- Requêtes SQL organisées par thème
│   ├── data_loader.py          <- Chargement SQL -> pandas DataFrame
│   ├── data_cleaning.py        <- Nettoyage des données
│   ├── analysis.py             <- Analyses et KPIs
│   ├── visualization.py        <- Graphiques matplotlib/seaborn
│   └── utils.py                <- Fonctions utilitaires partagées
│
├── reports/
│   └── final_report.md         <- Rapport final généré
│
├── requirements.txt            <- Dépendances Python
└── main.py                     <- Point d'entrée CLI
```

### Rôle de chaque fichier

| Fichier | Rôle | Responsabilité |
|---------|------|----------------|
| `data/chinook.db` | Données | Base SQLite avec les 11 tables Chinook |
| `src/db_connection.py` | Infrastructure | Ouvrir/fermer la connexion, Context Manager |
| `src/queries.py` | Requêtes SQL | Centralise toutes les requêtes (pas de SQL éparpillé) |
| `src/data_loader.py` | Interface | Charge le résultat des requêtes en DataFrames |
| `src/data_cleaning.py` | Qualité | Nettoie NaN, types, doublons |
| `src/analysis.py` | Métier | KPIs, agrégations, statistiques |
| `src/visualization.py` | Communication | Graphiques prêts pour les rapports |
| `src/utils.py` | Support | Logging, décorateurs, constantes |
| `main.py` | Orchestration | Lance les analyses via CLI |
| `requirements.txt` | Dépendances | Liste des bibliothèques à installer |

---

## 4. Installation et setup

### Étape 1 : Télécharger la base Chinook

```bash
# Option A : téléchargement direct via curl (Linux/Mac)
curl -L -o data/chinook.db \
  "https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"

# Option B : via Python (Windows-compatible)
python -c "
import urllib.request, pathlib
pathlib.Path('data').mkdir(exist_ok=True)
url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
urllib.request.urlretrieve(url, 'data/chinook.db')
print('Base téléchargée : data/chinook.db')
"

# Option C : téléchargement manuel
# -> https://github.com/lerocha/chinook-database
# -> ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
# -> Renommer en chinook.db et placer dans data/
```

### Étape 2 : Créer l'environnement Python

```bash
# Créer le dossier du projet
mkdir datainsight_sql
cd datainsight_sql

# Créer les sous-dossiers
mkdir -p data notebooks src reports

# Créer les fichiers __init__.py (rendent src/ importable)
touch src/__init__.py

# Créer le fichier requirements.txt
cat > requirements.txt << 'EOF'
pandas==2.2.0
numpy==1.26.4
matplotlib==3.8.2
seaborn==0.13.2
plotly==5.18.0
jupyter==1.0.0
ipykernel==6.29.0
sqlalchemy==2.0.28
python-dotenv==1.0.1
tabulate==0.9.0
EOF

# Installer les dépendances
pip install -r requirements.txt --break-system-packages
```

### Étape 3 : Vérifier l'installation

```python
# test_setup.py — lance ce script pour vérifier que tout fonctionne
import sqlite3
import pandas as pd
import pathlib

# Vérification de la base
chemin_db = pathlib.Path("data/chinook.db")
assert chemin_db.exists(), f"ERREUR : {chemin_db} introuvable"

# Connexion test
conn = sqlite3.connect(chemin_db)
cursor = conn.cursor()

# Lister les tables
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = [row[0] for row in cursor.fetchall()]
print(f"[OK] Connexion OK — {len(tables)} tables trouvées :")
for t in sorted(tables):
    cursor.execute(f"SELECT COUNT(*) FROM {t}")
    n = cursor.fetchone()[0]
    print(f"   {t:<20} : {n:>6,} lignes")

conn.close()
print("\n[OK] Setup complet — prêt pour DataInsight Pro SQL!")
```

Résultat attendu :
```
[OK] Connexion OK — 11 tables trouvées :
   Album                :    347 lignes
   Artist               :    275 lignes
   Customer             :     59 lignes
   Employee             :      8 lignes
   Genre                :     25 lignes
   Invoice              :    412 lignes
   InvoiceLine          :  2 240 lignes
   MediaType            :      5 lignes
   Playlist             :     18 lignes
   PlaylistTrack        :  8 715 lignes
   Track                :  3 503 lignes

[OK] Setup complet — prêt pour DataInsight Pro SQL!
```

---

## 5. La base de données Chinook — analyse complète

### Vue d'ensemble du schéma relationnel

```
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   Artist    │1   N│    Album    │1   N│    Track    │
│  ArtistId   ├────[BLACK_RIGHT-POINTING_POINTER]│  ArtistId   │     │   AlbumId   │
│  Name       │     │  AlbumId    ├────[BLACK_RIGHT-POINTING_POINTER]│   TrackId   │
└─────────────┘     │  Title      │     │   Name      │
                    └─────────────┘     │   GenreId   ├──[BLACK_RIGHT-POINTING_POINTER] Genre
                                        │ MediaTypeId ├──[BLACK_RIGHT-POINTING_POINTER] MediaType
                                        │   Millisec. │
                                        │   Bytes     │
                                        │   UnitPrice │
                                        └──────┬──────┘
                                               │ N
                                    ┌──────────[BLACK_DOWN-POINTING_TRIANGLE]──────────┐
                                    │    PlaylistTrack     │
                                    │  (table de liaison)  │
                                    │   PlaylistId         │
                                    │   TrackId            │
                                    └──────────────────────┘
                                               │
                                    ┌──────────[BLACK_DOWN-POINTING_TRIANGLE]──────────┐
                                    │      Playlist        │
                                    │   PlaylistId         │
                                    │   Name               │
                                    └──────────────────────┘

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  Employee   │1   N│  Customer   │1   N│   Invoice   │
│ EmployeeId  ├────[BLACK_RIGHT-POINTING_POINTER]│ SupportRepId│     │ CustomerId  │
│ LastName    │     │ CustomerId  ├────[BLACK_RIGHT-POINTING_POINTER]│ InvoiceId   │
│ FirstName   │     │ FirstName   │     │ InvoiceDate │
│ Title       │     │ Country     │     │ Total       │
│ ReportsTo   │     │ Email       │     └──────┬──────┘
└─────────────┘     └─────────────┘            │ 1:N
                                        ┌──────[BLACK_DOWN-POINTING_TRIANGLE]──────┐
                                        │ InvoiceLine │
                                        │ InvoiceId   │
                                        │ TrackId     │
                                        │ Quantity    │
                                        │ UnitPrice   │
                                        └─────────────┘
```

---

### Table 1 : `Artist` (275 lignes)

**Rôle** : Référentiel des artistes musicaux.

```sql
CREATE TABLE Artist (
    ArtistId  INTEGER  NOT NULL,  -- PRIMARY KEY : identifiant unique
    Name      NVARCHAR(120),       -- Nom de l'artiste (peut être NULL)

    CONSTRAINT PK_Artist PRIMARY KEY (ArtistId)
);
```

**Colonnes** :

| Colonne | Type SQL | Nullable | Rôle |
|---------|----------|----------|------|
| `ArtistId` | INTEGER | NON | Clé primaire (PK) — identifiant auto-incrémenté |
| `Name` | NVARCHAR(120) | OUI | Nom de l'artiste (ex : "AC/DC", "Miles Davis") |

**Exemples de données** :
```
ArtistId | Name
---------|--------------------
1        | AC/DC
2        | Accept
3        | Aerosmith
4        | Alanis Morissette
5        | Alice In Chains
```

**Relations** :
- `ArtistId` -> référencé par `Album.ArtistId` (relation 1:N — un artiste a plusieurs albums)

---

### Table 2 : `Album` (347 lignes)

**Rôle** : Catalogue des albums musicaux.

```sql
CREATE TABLE Album (
    AlbumId   INTEGER      NOT NULL,  -- PK
    Title     NVARCHAR(160) NOT NULL,  -- Titre de l'album
    ArtistId  INTEGER      NOT NULL,  -- FK vers Artist

    CONSTRAINT PK_Album PRIMARY KEY (AlbumId),
    CONSTRAINT FK_AlbumArtistId FOREIGN KEY (ArtistId)
        REFERENCES Artist (ArtistId) ON DELETE NO ACTION ON UPDATE NO ACTION
);
```

**Colonnes** :

| Colonne | Type SQL | Nullable | Rôle |
|---------|----------|----------|------|
| `AlbumId` | INTEGER | NON | Clé primaire |
| `Title` | NVARCHAR(160) | NON | Titre de l'album |
| `ArtistId` | INTEGER | NON | FK -> `Artist.ArtistId` |

**Relations** :
- `ArtistId` est une **Foreign Key (FK)** qui référence `Artist.ArtistId`
- `AlbumId` est référencé par `Track.AlbumId`

**Qu'est-ce qu'une Foreign Key ?**
Une FK est une contrainte qui garantit que la valeur dans une colonne
existe bien dans la table référencée. Si `Album.ArtistId = 5`,
alors il DOIT exister un enregistrement avec `Artist.ArtistId = 5`.
Cela garantit l'**intégrité référentielle**.

---

### Table 3 : `Track` (3 503 lignes) — Table centrale

**Rôle** : Catalogue de toutes les pistes musicales. C'est la table la plus
importante pour les analyses de ventes.

```sql
CREATE TABLE Track (
    TrackId      INTEGER       NOT NULL,  -- PK
    Name         NVARCHAR(200) NOT NULL,  -- Titre de la piste
    AlbumId      INTEGER,                 -- FK -> Album (nullable : peut être orpheline)
    MediaTypeId  INTEGER       NOT NULL,  -- FK -> MediaType
    GenreId      INTEGER,                 -- FK -> Genre (nullable)
    Composer     NVARCHAR(220),           -- Compositeur
    Milliseconds INTEGER       NOT NULL,  -- Durée en millisecondes
    Bytes        INTEGER,                 -- Taille du fichier
    UnitPrice    NUMERIC(10,2) NOT NULL,  -- Prix unitaire en USD

    CONSTRAINT PK_Track PRIMARY KEY (TrackId),
    CONSTRAINT FK_TrackAlbumId     FOREIGN KEY (AlbumId)     REFERENCES Album (AlbumId),
    CONSTRAINT FK_TrackGenreId     FOREIGN KEY (GenreId)      REFERENCES Genre (GenreId),
    CONSTRAINT FK_TrackMediaTypeId FOREIGN KEY (MediaTypeId)  REFERENCES MediaType (MediaTypeId)
);
```

**Colonnes** :

| Colonne | Type SQL | Nullable | Rôle |
|---------|----------|----------|------|
| `TrackId` | INTEGER | NON | Clé primaire |
| `Name` | NVARCHAR(200) | NON | Titre de la piste |
| `AlbumId` | INTEGER | OUI | FK -> `Album.AlbumId` |
| `MediaTypeId` | INTEGER | NON | FK -> `MediaType.MediaTypeId` |
| `GenreId` | INTEGER | OUI | FK -> `Genre.GenreId` |
| `Composer` | NVARCHAR(220) | OUI | Compositeur(s) |
| `Milliseconds` | INTEGER | NON | Durée en ms (ex: 240000 = 4 min) |
| `Bytes` | INTEGER | OUI | Taille en octets |
| `UnitPrice` | NUMERIC(10,2) | NON | Prix en USD (0.99 ou 1.99) |

**Particularité** : `AlbumId` et `GenreId` sont nullables — certaines pistes
n'appartiennent pas à un album ou n'ont pas de genre assigné.

---

### Table 4 : `Genre` (25 lignes)

**Rôle** : Référentiel des genres musicaux.

```sql
CREATE TABLE Genre (
    GenreId  INTEGER       NOT NULL,
    Name     NVARCHAR(120),

    CONSTRAINT PK_Genre PRIMARY KEY (GenreId)
);
```

**Exemples** :
```
GenreId | Name
--------|-------------
1       | Rock
2       | Jazz
3       | Metal
4       | Alternative & Punk
5       | Rock And Roll
6       | Blues
7       | Latin
8       | Reggae
9       | Pop
10      | Soundtrack
```

---

### Table 5 : `MediaType` (5 lignes)

**Rôle** : Format du fichier audio.

```sql
CREATE TABLE MediaType (
    MediaTypeId  INTEGER       NOT NULL,
    Name         NVARCHAR(120),
    CONSTRAINT PK_MediaType PRIMARY KEY (MediaTypeId)
);
```

**Toutes les valeurs** :
```
MediaTypeId | Name
------------|--------------------------------
1           | MPEG audio file
2           | Protected AAC audio file
3           | Protected MPEG-4 video file
4           | Purchased AAC audio file
5           | AAC audio file
```

---

### Table 6 : `Playlist` (18 lignes) + `PlaylistTrack` (8 715 lignes)

**Rôle** : Les playlists et l'association playlists <-> pistes.

`PlaylistTrack` est une **table de liaison** (aussi appelée table associative
ou table de jonction). Elle résout la relation **Many-to-Many (N:N)** entre
`Playlist` et `Track` :
- Une playlist contient plusieurs pistes
- Une piste peut être dans plusieurs playlists

```sql
-- Table PlaylistTrack : clé primaire COMPOSITE (deux colonnes)
CREATE TABLE PlaylistTrack (
    PlaylistId  INTEGER  NOT NULL,  -- FK -> Playlist
    TrackId     INTEGER  NOT NULL,  -- FK -> Track

    CONSTRAINT PK_PlaylistTrack PRIMARY KEY (PlaylistId, TrackId)
    -- La combinaison (PlaylistId, TrackId) est unique -> pas de doublon
);
```

**Concept de clé primaire composite** :
Au lieu d'une seule colonne PK, on combine **deux colonnes** pour identifier
une ligne de manière unique. La paire `(PlaylistId=1, TrackId=42)` ne peut
apparaître qu'une seule fois dans la table.

---

### Table 7 : `Customer` (59 lignes)

**Rôle** : Base clients. Table fondamentale pour les analyses commerciales.

```sql
CREATE TABLE Customer (
    CustomerId   INTEGER       NOT NULL,
    FirstName    NVARCHAR(40)  NOT NULL,
    LastName     NVARCHAR(20)  NOT NULL,
    Company      NVARCHAR(80),          -- Entreprise (nullable)
    Address      NVARCHAR(70),
    City         NVARCHAR(40),
    State        NVARCHAR(40),          -- État/province (nullable)
    Country      NVARCHAR(40),
    PostalCode   NVARCHAR(10),
    Phone        NVARCHAR(24),
    Fax          NVARCHAR(24),          -- Fax (souvent NULL)
    Email        NVARCHAR(60)  NOT NULL,
    SupportRepId INTEGER,               -- FK -> Employee (agent assigné)

    CONSTRAINT PK_Customer PRIMARY KEY (CustomerId),
    CONSTRAINT FK_CustomerSupportRepId FOREIGN KEY (SupportRepId)
        REFERENCES Employee (EmployeeId)
);
```

**Colonnes importantes** :

| Colonne | Type | Nullable | Analyse possible |
|---------|------|----------|-----------------|
| `Country` | NVARCHAR(40) | OUI | Revenus par pays |
| `SupportRepId` | INTEGER | OUI | Performance des agents |
| `Email` | NVARCHAR(60) | NON | Contact unique |
| `State` | NVARCHAR(40) | OUI | Analyse régionale |

**Note** : 59 clients dans 24 pays différents — parfait pour une analyse géographique.

---

### Table 8 : `Employee` (8 lignes)

**Rôle** : Équipe Chinook (agents commerciaux, managers, IT).

```sql
CREATE TABLE Employee (
    EmployeeId  INTEGER       NOT NULL,
    LastName    NVARCHAR(20)  NOT NULL,
    FirstName   NVARCHAR(20)  NOT NULL,
    Title       NVARCHAR(30),           -- Intitulé du poste
    ReportsTo   INTEGER,                -- FK -> Employee (auto-référence !)
    BirthDate   DATETIME,
    HireDate    DATETIME,
    Address     NVARCHAR(70),
    City        NVARCHAR(40),
    State       NVARCHAR(40),
    Country     NVARCHAR(40),
    PostalCode  NVARCHAR(10),
    Phone       NVARCHAR(24),
    Fax         NVARCHAR(24),
    Email       NVARCHAR(60),

    CONSTRAINT PK_Employee PRIMARY KEY (EmployeeId),
    CONSTRAINT FK_EmployeeReportsTo FOREIGN KEY (ReportsTo)
        REFERENCES Employee (EmployeeId)
        -- Auto-référence : un employé reporte à un autre employé
);
```

**Particularité importante** : La colonne `ReportsTo` est une **auto-référence** —
elle référence la même table (`Employee`). Cela modélise une hiérarchie :
- Le PDG a `ReportsTo = NULL` (ne reporte à personne)
- Le manager a `ReportsTo = PDG.EmployeeId`
- Les agents ont `ReportsTo = Manager.EmployeeId`

```
EmployeeId=1 (General Manager)    -> ReportsTo = NULL
EmployeeId=2 (Sales Manager)      -> ReportsTo = 1
EmployeeId=3 (IT Manager)         -> ReportsTo = 1
EmployeeId=4 (Sales Support Agent)-> ReportsTo = 2
EmployeeId=5 (Sales Support Agent)-> ReportsTo = 2
EmployeeId=6 (Sales Support Agent)-> ReportsTo = 2
EmployeeId=7 (IT Staff)           -> ReportsTo = 3
EmployeeId=8 (IT Staff)           -> ReportsTo = 3
```

---

### Table 9 : `Invoice` (412 lignes)

**Rôle** : Factures clients. Table centrale pour l'analyse financière.

```sql
CREATE TABLE Invoice (
    InvoiceId         INTEGER        NOT NULL,
    CustomerId        INTEGER        NOT NULL,  -- FK -> Customer
    InvoiceDate       DATETIME       NOT NULL,  -- Date de la facture
    BillingAddress    NVARCHAR(70),
    BillingCity       NVARCHAR(40),
    BillingState      NVARCHAR(40),
    BillingCountry    NVARCHAR(40),
    BillingPostalCode NVARCHAR(10),
    Total             NUMERIC(10,2)  NOT NULL,  -- Montant total

    CONSTRAINT PK_Invoice PRIMARY KEY (InvoiceId),
    CONSTRAINT FK_InvoiceCustomerId FOREIGN KEY (CustomerId)
        REFERENCES Customer (CustomerId)
);
```

**Colonnes clés pour l'analyse** :

| Colonne | Usage analytique |
|---------|-----------------|
| `InvoiceDate` | Analyse temporelle (tendances, saisonnalité) |
| `BillingCountry` | Revenus par pays |
| `Total` | Panier moyen, revenus totaux |
| `CustomerId` | LTV (Lifetime Value) par client |

---

### Table 10 : `InvoiceLine` (2 240 lignes)

**Rôle** : Lignes détaillées de chaque facture. C'est ici que se trouve
le détail de ce qui a été acheté.

```sql
CREATE TABLE InvoiceLine (
    InvoiceLineId  INTEGER        NOT NULL,
    InvoiceId      INTEGER        NOT NULL,  -- FK -> Invoice
    TrackId        INTEGER        NOT NULL,  -- FK -> Track
    UnitPrice      NUMERIC(10,2)  NOT NULL,  -- Prix au moment de l'achat
    Quantity       INTEGER        NOT NULL,  -- Toujours 1 dans Chinook

    CONSTRAINT PK_InvoiceLine PRIMARY KEY (InvoiceLineId),
    CONSTRAINT FK_InvoiceLineInvoiceId FOREIGN KEY (InvoiceId)
        REFERENCES Invoice (InvoiceId),
    CONSTRAINT FK_InvoiceLineTrackId FOREIGN KEY (TrackId)
        REFERENCES Track (TrackId)
);
```

**Note importante** : `UnitPrice` dans `InvoiceLine` peut différer de
`Track.UnitPrice` — le prix peut avoir changé depuis l'achat.
C'est pour cela qu'on stocke le prix au moment de la vente.

**Relation Invoice <-> InvoiceLine** :
```
Invoice (1) ──────────── (N) InvoiceLine
Facture n°1 peut avoir 5 lignes (5 pistes achetées)
```

---

### Résumé des relations

```sql
-- Toutes les relations de la base Chinook :

Artist    (1) ────── (N) Album
Album     (1) ────── (N) Track
Genre     (1) ────── (N) Track
MediaType (1) ────── (N) Track
Track     (N) ────── (N) Playlist    [via PlaylistTrack]
Customer  (1) ────── (N) Invoice
Invoice   (1) ────── (N) InvoiceLine
Track     (1) ────── (N) InvoiceLine
Employee  (1) ────── (N) Customer    [agent de support]
Employee  (1) ────── (N) Employee    [hiérarchie interne]
```

---

## 6. Code : `src/db_connection.py`

```python
"""
db_connection.py — Gestion de la connexion à la base de données SQLite

Ce module centralise toute la logique de connexion.
Utiliser un module dédié présente plusieurs avantages :
- Un seul endroit à modifier si on change de base de données
- Gestion propre des connexions (ouverture/fermeture)
- Support du Context Manager (with statement)
"""

# ============================================================
# IMPORTS
# ============================================================
import sqlite3
# sqlite3 : module STANDARD Python (inclus sans installation)
# Il permet de travailler avec des bases de données SQLite
# SQLite = base de données stockée dans UN SEUL FICHIER .db

import pandas as pd
# pandas : pour convertir les résultats SQL en DataFrames

from pathlib import Path
# pathlib : gestion des chemins de fichiers (multi-OS)
# Path("data/chinook.db") fonctionne sur Windows, Mac ET Linux

from typing import Optional, List, Tuple, Any
# typing : annotations de types pour la documentation et l'IDE

import logging
# logging : journalisation des événements (plus professionnel que print)

# ============================================================
# CONFIGURATION DU LOGGING
# ============================================================
# logging.getLogger(__name__) crée un logger avec le nom du module
# Cela permet de filtrer les logs par module dans les gros projets
logger = logging.getLogger(__name__)


# ============================================================
# CONSTANTES
# ============================================================
# Path(__file__) : chemin absolu du fichier db_connection.py
# .parent       : dossier parent (= src/)
# .parent       : dossier parent de src/ (= datainsight_sql/)
# / "data"      : sous-dossier data/
# / "chinook.db": le fichier de la base
DB_PATH = Path(__file__).parent.parent / "data" / "chinook.db"
# Résultat exemple : /home/user/datainsight_sql/data/chinook.db


# ============================================================
# CLASSE PRINCIPALE : DatabaseConnection
# ============================================================
class DatabaseConnection:
    """
    Gestionnaire de connexion SQLite pour la base Chinook.

    Cette classe implémente le protocole Context Manager Python,
    ce qui permet de l'utiliser avec le mot-clé 'with' :

        with DatabaseConnection() as conn:
            df = pd.read_sql("SELECT * FROM Artist", conn)
        # La connexion est fermée automatiquement ici

    Avantage du Context Manager :
        La connexion est TOUJOURS fermée, même si une exception survient.
        Sans cela, une erreur dans le with block laisserait la connexion ouverte
        -> risque de corruption de la base ou de fuite de ressources.

    Args:
        db_path (Path, optional) : Chemin vers le fichier .db
            Par défaut = DB_PATH (défini dans les constantes)

    Attributes:
        db_path (Path)         : Chemin de la base
        connection (sqlite3.Connection) : Connexion active (None si fermée)

    Example:
        >>> with DatabaseConnection() as conn:
        ...     df = pd.read_sql("SELECT COUNT(*) FROM Track", conn)
        ...     print(df)
    """

    def __init__(self, db_path: Optional[Path] = None):
        # Si db_path n'est pas fourni, on utilise la constante par défaut
        self.db_path = db_path or DB_PATH

        # La connexion est None tant qu'on n'a pas appelé __enter__
        self.connection: Optional[sqlite3.Connection] = None

    def __enter__(self) -> sqlite3.Connection:
        """
        Méthode appelée automatiquement à l'entrée du 'with' block.

        Elle ouvre la connexion et la retourne.
        La valeur retournée est assignée à la variable après 'as'.

        Returns:
            sqlite3.Connection : La connexion SQLite active

        Raises:
            FileNotFoundError : Si le fichier .db n'existe pas
        """
        # Vérification que le fichier existe
        if not self.db_path.exists():
            raise FileNotFoundError(
                f"Base de données introuvable : {self.db_path}\n"
                f"Lance : python -c \"import urllib.request; "
                f"urllib.request.urlretrieve("
                f"'https://github.com/lerocha/chinook-database/raw/master/"
                f"ChinookDatabase/DataSources/Chinook_Sqlite.sqlite', "
                f"'data/chinook.db')\""
            )

        # Ouverture de la connexion SQLite
        # check_same_thread=False : permet d'utiliser la connexion depuis
        # plusieurs threads (utile dans Jupyter)
        self.connection = sqlite3.connect(
            self.db_path,
            check_same_thread=False
        )

        # Activation du mode WAL (Write-Ahead Logging)
        # Améliore les performances en lecture concurrente
        self.connection.execute("PRAGMA journal_mode=WAL")

        # Activation des clés étrangères (désactivées par défaut dans SQLite !)
        # IMPORTANT : sans cette ligne, SQLite ignore les contraintes FK
        self.connection.execute("PRAGMA foreign_keys=ON")

        # row_factory : fait que les résultats sont des dict-like objects
        # au lieu de tuples simples -> accès par nom de colonne
        self.connection.row_factory = sqlite3.Row

        logger.info(f"Connexion ouverte : {self.db_path}")
        return self.connection

    def __exit__(self, exc_type, exc_val, exc_tb) -> bool:
        """
        Méthode appelée automatiquement à la sortie du 'with' block.

        Elle ferme la connexion, que le bloc se termine normalement
        ou avec une exception.

        Args:
            exc_type : Type de l'exception (None si pas d'erreur)
            exc_val  : Valeur de l'exception
            exc_tb   : Traceback de l'exception

        Returns:
            bool : False = on ne supprime pas les exceptions
                   (elles seront quand même levées après __exit__)
        """
        if self.connection:
            if exc_type:
                # Une exception s'est produite -> on annule (rollback)
                self.connection.rollback()
                logger.error(f"Erreur SQL : {exc_val} — rollback effectué")
            else:
                # Pas d'exception -> on valide (commit)
                self.connection.commit()

            self.connection.close()
            self.connection = None
            logger.info("Connexion fermée")

        # Retourner False : on ne supprime pas les exceptions
        return False


# ============================================================
# FONCTIONS UTILITAIRES
# ============================================================

def lister_tables(conn: sqlite3.Connection) -> List[str]:
    """
    Retourne la liste de toutes les tables de la base.

    sqlite_master est une table système SQLite qui contient
    les métadonnées : tables, vues, index, triggers.

    Args:
        conn : Connexion SQLite active

    Returns:
        List[str] : Noms des tables triés alphabétiquement

    Example:
        >>> with DatabaseConnection() as conn:
        ...     tables = lister_tables(conn)
        ...     print(tables)
        ['Album', 'Artist', 'Customer', ...]
    """
    # sqlite_master : table interne SQLite avec les objets de la base
    # type='table' : on filtre pour n'avoir que les tables (pas les vues)
    query = """
        SELECT name
        FROM sqlite_master
        WHERE type = 'table'
        ORDER BY name
    """
    # cursor() crée un objet Cursor pour exécuter les requêtes
    cursor = conn.cursor()
    cursor.execute(query)

    # fetchall() récupère TOUS les résultats en mémoire (liste de Row)
    # [row[0] for row in ...] : list comprehension -> extrait le premier champ
    return [row[0] for row in cursor.fetchall()]


def infos_table(conn: sqlite3.Connection, nom_table: str) -> pd.DataFrame:
    """
    Retourne les informations sur les colonnes d'une table.

    PRAGMA table_info() est une commande SQLite qui retourne
    le schéma d'une table (équivalent de DESCRIBE en MySQL).

    Args:
        conn       : Connexion SQLite
        nom_table  : Nom de la table à analyser

    Returns:
        pd.DataFrame : Schéma de la table avec colonnes :
            cid, name, type, notnull, dflt_value, pk

    Example:
        >>> with DatabaseConnection() as conn:
        ...     schema = infos_table(conn, "Customer")
        ...     print(schema[["name", "type", "notnull", "pk"]])
    """
    # PRAGMA table_info retourne une ligne par colonne avec :
    # cid       : numéro d'ordre de la colonne
    # name      : nom de la colonne
    # type      : type SQL (INTEGER, NVARCHAR, etc.)
    # notnull   : 1 si NOT NULL, 0 si nullable
    # dflt_value: valeur par défaut (NULL si aucune)
    # pk        : 1 si fait partie de la PK, 0 sinon
    query = f"PRAGMA table_info({nom_table})"

    # pd.read_sql() : exécute la requête ET convertit en DataFrame en une ligne
    return pd.read_sql(query, conn)


def compter_lignes(conn: sqlite3.Connection) -> pd.DataFrame:
    """
    Compte le nombre de lignes dans chaque table.

    Utilise une requête UNION ALL pour agréger les comptages.

    Args:
        conn : Connexion SQLite

    Returns:
        pd.DataFrame : Table avec colonnes table_name et nb_lignes,
            triée par nb_lignes décroissant.
    """
    tables = lister_tables(conn)

    # On construit dynamiquement une requête UNION ALL
    # UNION ALL combine les résultats de plusieurs SELECT
    # sans supprimer les doublons (plus rapide que UNION)
    sous_requetes = [
        f"SELECT '{t}' AS table_name, COUNT(*) AS nb_lignes FROM {t}"
        for t in tables
    ]
    # join() concatène les sous-requêtes avec " UNION ALL " entre elles
    query = " UNION ALL ".join(sous_requetes) + " ORDER BY nb_lignes DESC"

    return pd.read_sql(query, conn)


def executer_requete(
    conn: sqlite3.Connection,
    query: str,
    params: Optional[Tuple] = None
) -> pd.DataFrame:
    """
    Exécute une requête SQL et retourne le résultat en DataFrame.

    Utilise des paramètres pour éviter les injections SQL.

    Args:
        conn   : Connexion SQLite
        query  : Requête SQL (utiliser ? comme placeholder)
        params : Valeurs pour les placeholders (tuple)

    Returns:
        pd.DataFrame : Résultat de la requête

    Example:
        >>> with DatabaseConnection() as conn:
        ...     df = executer_requete(
        ...         conn,
        ...         "SELECT * FROM Customer WHERE Country = ?",
        ...         ("France",)
        ...     )
    """
    # pd.read_sql avec params= protège contre les injections SQL
    # NE JAMAIS faire : f"SELECT * FROM Customer WHERE Country = '{pays}'"
    # TOUJOURS faire  : params=("France",) avec ? dans la requête
    return pd.read_sql(query, conn, params=params)
```

---

## 7. Code : `src/utils.py`

```python
"""
utils.py — Fonctions utilitaires partagées

Ce module contient des outils génériques réutilisables
dans tous les autres modules du projet.
"""

import time           # time.time() : mesure le temps d'exécution
import functools      # functools.wraps : copie les métadonnées d'une fonction
import logging        # logging : journalisation
from pathlib import Path
from datetime import datetime
import pandas as pd


# ============================================================
# CONFIGURATION GLOBALE DU LOGGING
# ============================================================

def configurer_logging(niveau: str = "INFO") -> None:
    """
    Configure le système de journalisation pour tout le projet.

    Le logging est supérieur aux print() pour plusieurs raisons :
    - Niveaux (DEBUG, INFO, WARNING, ERROR, CRITICAL)
    - Horodatage automatique
    - Filtrage par niveau
    - Écriture dans des fichiers

    Args:
        niveau : Niveau minimum de log ("DEBUG", "INFO", "WARNING", etc.)

    Example:
        >>> configurer_logging("DEBUG")
        >>> logging.info("Démarrage")
        2024-01-15 10:30:00 - INFO     - Démarrage
    """
    # basicConfig : configuration globale appliquée à tous les loggers
    logging.basicConfig(
        level=getattr(logging, niveau.upper()),
        # getattr(logging, "INFO") -> logging.INFO -> valeur numérique 20
        # Cela permet de passer le niveau en string ("INFO") plutôt qu'en entier

        format="%(asctime)s - %(levelname)-8s - %(name)s - %(message)s",
        # %(asctime)s   : horodatage automatique
        # %(levelname)s : niveau (INFO, WARNING, etc.)
        # -8s           : padding à 8 caractères pour l'alignement
        # %(name)s      : nom du logger (= nom du module)
        # %(message)s   : le message de log

        datefmt="%Y-%m-%d %H:%M:%S",
        # Format de la date : 2024-01-15 10:30:00

        handlers=[
            # StreamHandler : affiche dans la console (stdout)
            logging.StreamHandler(),
            # FileHandler : écrit dans un fichier
            logging.FileHandler("logs/datainsight.log", mode="a", encoding="utf-8"),
        ]
    )
    Path("logs").mkdir(exist_ok=True)
    # exist_ok=True : ne lève pas d'erreur si le dossier existe déjà


# ============================================================
# DÉCORATEUR : @timeit
# ============================================================

def timeit(func):
    """
    Décorateur qui mesure et affiche le temps d'exécution d'une fonction.

    Un décorateur est une fonction qui prend une fonction en argument
    et retourne une nouvelle fonction enrichie.

    Utilisation :
        @timeit
        def ma_fonction():
            # code long...

    Args:
        func : La fonction à décorer

    Returns:
        wrapper : La fonction enrichie avec la mesure du temps

    Example:
        >>> @timeit
        ... def charger_donnees():
        ...     time.sleep(0.1)
        charger_donnees() exécutée en 0.100s
    """
    @functools.wraps(func)
    # @functools.wraps(func) : copie le nom, la docstring, etc. de func
    # sur wrapper -> func.__name__ reste "charger_donnees" et non "wrapper"

    def wrapper(*args, **kwargs):
        # *args   : capture tous les arguments positionnels
        # **kwargs: capture tous les arguments nommés
        debut = time.perf_counter()
        # perf_counter() : horloge haute résolution (plus précis que time())

        try:
            # Appel de la vraie fonction avec ses arguments
            resultat = func(*args, **kwargs)
            duree = time.perf_counter() - debut
            logger = logging.getLogger(func.__module__)
            logger.info(f"{func.__name__}() exécutée en {duree:.3f}s")
            return resultat
        except Exception as e:
            duree = time.perf_counter() - debut
            logger = logging.getLogger(func.__module__)
            logger.error(f"{func.__name__}() échoué en {duree:.3f}s : {e}")
            raise  # On re-lève l'exception pour ne pas la masquer

    return wrapper


# ============================================================
# CONSTANTES DU PROJET
# ============================================================

# Path(__file__).parent.parent = racine du projet (datainsight_sql/)
ROOT_DIR    = Path(__file__).parent.parent
DATA_DIR    = ROOT_DIR / "data"
REPORTS_DIR = ROOT_DIR / "reports"
FIGURES_DIR = REPORTS_DIR / "figures"

# Création des dossiers si inexistants
for dossier in [DATA_DIR, REPORTS_DIR, FIGURES_DIR]:
    dossier.mkdir(parents=True, exist_ok=True)


# ============================================================
# FONCTIONS UTILITAIRES
# ============================================================

def afficher_dataframe(df: pd.DataFrame, titre: str = "", max_rows: int = 10) -> None:
    """
    Affiche un DataFrame de manière lisible dans la console.

    Args:
        df       : DataFrame à afficher
        titre    : Titre optionnel affiché avant le tableau
        max_rows : Nombre max de lignes à afficher
    """
    if titre:
        print(f"\n{'='*60}")
        print(f"  {titre}")
        print(f"{'='*60}")

    # head(max_rows) : garde seulement les max_rows premières lignes
    # to_string() : convertit le DataFrame en string lisible
    # index=False : ne pas afficher l'index (0, 1, 2...)
    print(df.head(max_rows).to_string(index=False))
    print(f"\n-> {len(df):,} lignes × {len(df.columns)} colonnes")


def formater_monnaie(montant: float) -> str:
    """
    Formate un nombre en montant monétaire USD.

    Args:
        montant : Valeur numérique

    Returns:
        str : Chaîne formatée (ex: "$1,234.56")

    Example:
        >>> formater_monnaie(1234.567)
        '$1,234.57'
    """
    return f"${montant:,.2f}"
    # :,   -> séparateur de milliers (1,234)
    # .2f  -> 2 décimales (1,234.57)
    # f"$" -> préfixe dollar


def rapport_qualite_sql(df: pd.DataFrame) -> pd.DataFrame:
    """
    Calcule un rapport de qualité pour un DataFrame chargé depuis SQL.

    Vérifie : valeurs manquantes, types, valeurs uniques.

    Args:
        df : DataFrame à analyser

    Returns:
        pd.DataFrame : Rapport avec une ligne par colonne
    """
    rapport = pd.DataFrame({
        "colonne":          df.columns,
        "type":             df.dtypes.values,
        "nb_non_null":      df.count().values,
        "nb_null":          df.isnull().sum().values,
        "pct_null":         (df.isnull().mean() * 100).round(2).values,
        "nb_uniques":       df.nunique().values,
        "exemple_valeur":   [df[c].dropna().iloc[0] if not df[c].dropna().empty
                             else None for c in df.columns],
    })

    # Calcul du % de null pour chaque colonne
    rapport["pct_null"] = rapport["pct_null"].map(lambda x: f"{x:.1f}%")

    return rapport
```

---

## 8. Code : `main.py` — point d'entrée

```python
"""
main.py — Interface en ligne de commande de DataInsight Pro SQL

Usage :
    python main.py --action info       # Affiche les infos de la base
    python main.py --action explore    # Exploration de toutes les tables
    python main.py --action query      # Requêtes d'analyse
    python main.py --action all        # Tout exécuter

Ce fichier sert de point d'entrée unique au projet.
On y importe les modules de src/ et on orchestre leur appel.
"""

import argparse           # argparse : module standard pour les CLIs
import sys                # sys : interaction avec l'interpréteur Python
from pathlib import Path  # pathlib : gestion des chemins

# Ajout du dossier racine au PYTHONPATH
# Cela permet d'importer les modules de src/ depuis n'importe quel dossier
sys.path.insert(0, str(Path(__file__).parent))

# Imports des modules internes
from src.utils import configurer_logging, afficher_dataframe, formater_monnaie
from src.db_connection import DatabaseConnection, lister_tables, compter_lignes, infos_table
import logging

# Configuration du logging dès le démarrage
configurer_logging("INFO")
logger = logging.getLogger(__name__)


# ============================================================
# ACTIONS DISPONIBLES
# ============================================================

def action_info() -> None:
    """
    Affiche les informations générales sur la base de données.
    Utile pour vérifier que tout fonctionne et comprendre la structure.
    """
    print("\n" + "="*65)
    print("  DATAINSIGHT PRO SQL — Chinook Music Database")
    print("="*65)

    with DatabaseConnection() as conn:
        # --- Liste des tables ---
        tables = lister_tables(conn)
        print(f"\n[GRAPHIQUE] Tables disponibles ({len(tables)}) :")
        for t in tables:
            print(f"   • {t}")

        # --- Comptage des lignes ---
        print("\n[HAUSSE] Volume de données :")
        df_counts = compter_lignes(conn)
        for _, row in df_counts.iterrows():
            # row["table_name"] : nom de la table
            # row["nb_lignes"]  : nombre de lignes
            barre = "█" * min(int(row["nb_lignes"] / 200), 30)
            print(f"   {row['table_name']:<20} {row['nb_lignes']:>6,} lignes  {barre}")

        # --- Total ---
        total = df_counts["nb_lignes"].sum()
        print(f"\n   TOTAL : {total:,} enregistrements")

    print("\n[OK] Base Chinook prête pour l'analyse !")


def action_explore(table_name: str = None) -> None:
    """
    Explore le schéma et les premières lignes d'une ou toutes les tables.

    Args:
        table_name : Nom de la table (None = toutes les tables)
    """
    with DatabaseConnection() as conn:
        tables = [table_name] if table_name else lister_tables(conn)

        for table in tables:
            print(f"\n{'─'*60}")
            print(f"  TABLE : {table}")
            print(f"{'─'*60}")

            # Schéma de la table
            schema = infos_table(conn, table)
            print("\nSchéma :")
            # iterrows() : itère sur les lignes du DataFrame
            for _, col in schema.iterrows():
                nullable = "[OK]" if not col["notnull"] else "[X]"
                pk_marker = " <- PK" if col["pk"] else ""
                print(f"  {col['name']:<25} {col['type']:<20} "
                      f"NULL:{nullable}{pk_marker}")

            # Premières lignes
            import pandas as pd
            df = pd.read_sql(f"SELECT * FROM {table} LIMIT 3", conn)
            print(f"\nExemples (3 premières lignes) :")
            print(df.to_string(index=False))


def action_query() -> None:
    """
    Exécute et affiche des requêtes SQL de démonstration.
    Préfigure les analyses de la Partie 2.
    """
    import pandas as pd

    with DatabaseConnection() as conn:

        # Requête 1 : Revenus par pays
        print("\n[GRAPHIQUE] REQUÊTE 1 : Top 10 pays par revenus")
        print("─"*50)
        query_1 = """
            SELECT
                BillingCountry          AS pays,
                COUNT(InvoiceId)        AS nb_factures,
                ROUND(SUM(Total), 2)    AS ca_total,
                ROUND(AVG(Total), 2)    AS panier_moyen
            FROM Invoice
            GROUP BY BillingCountry
            ORDER BY ca_total DESC
            LIMIT 10
        """
        df1 = pd.read_sql(query_1, conn)
        print(df1.to_string(index=False))

        # Requête 2 : Top genres musicaux
        print("\n[SON] REQUÊTE 2 : Top genres par nombre de ventes")
        print("─"*50)
        query_2 = """
            SELECT
                g.Name                  AS genre,
                COUNT(il.InvoiceLineId) AS nb_ventes,
                ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS revenus
            FROM InvoiceLine il
            JOIN Track t  ON il.TrackId  = t.TrackId
            JOIN Genre g  ON t.GenreId   = g.GenreId
            GROUP BY g.Name
            ORDER BY nb_ventes DESC
            LIMIT 10
        """
        df2 = pd.read_sql(query_2, conn)
        print(df2.to_string(index=False))


# ============================================================
# POINT D'ENTRÉE PRINCIPAL
# ============================================================

def creer_parser() -> argparse.ArgumentParser:
    """Configure le parser d'arguments CLI."""
    parser = argparse.ArgumentParser(
        description="DataInsight Pro SQL — Analyse Chinook Music Database",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Exemples :
  python main.py --action info
  python main.py --action explore
  python main.py --action explore --table Artist
  python main.py --action query
  python main.py --action all
        """
    )
    parser.add_argument(
        "--action",
        choices=["info", "explore", "query", "all"],
        default="info",
        help="Action à exécuter (défaut: info)"
    )
    parser.add_argument(
        "--table",
        default=None,
        help="Nom de la table (pour --action explore)"
    )
    return parser


def main():
    parser  = creer_parser()
    args    = parser.parse_args()
    logger.info(f"Action demandée : {args.action}")

    if args.action == "info" or args.action == "all":
        action_info()
    if args.action == "explore" or args.action == "all":
        action_explore(args.table)
    if args.action == "query" or args.action == "all":
        action_query()


if __name__ == "__main__":
    # __name__ == "__main__" : ce code n'est exécuté que si on lance
    # directement ce fichier (python main.py), pas si on l'importe
    main()
```

---

## 9. Bonnes pratiques SQL et Python

### Bonnes pratiques SQL

#### 1. Toujours nommer les colonnes (ne jamais utiliser `SELECT *`)

```sql
-- [X] MAUVAIS : opaque, fragile si la table change
SELECT * FROM Customer;

-- [OK] BON : explicite, documenté
SELECT
    CustomerId,
    FirstName,
    LastName,
    Country,
    Email
FROM Customer;
```

**Pourquoi ?** Si on ajoute une colonne à `Customer`, `SELECT *` retourne
maintenant une colonne de plus, ce qui peut casser le code Python qui
dépend de l'ordre ou du nombre de colonnes.

#### 2. Indentation et formatage SQL

```sql
-- [X] MAUVAIS : illisible
SELECT c.FirstName,c.LastName,SUM(i.Total) FROM Customer c JOIN Invoice i ON c.CustomerId=i.CustomerId GROUP BY c.CustomerId ORDER BY SUM(i.Total) DESC;

-- [OK] BON : lisible, maintenable
SELECT
    c.FirstName,
    c.LastName,
    SUM(i.Total)    AS total_achats
FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY
    c.CustomerId,
    c.FirstName,
    c.LastName
ORDER BY total_achats DESC;
```

#### 3. Paramètres pour éviter les injections SQL

```python
# [X] MAUVAIS : injection SQL possible
pays = input("Entrez un pays : ")
query = f"SELECT * FROM Customer WHERE Country = '{pays}'"
# Un utilisateur malveillant peut entrer : France' OR '1'='1
# -> requête devient : WHERE Country = 'France' OR '1'='1'
# -> retourne TOUS les clients !

# [OK] BON : paramètres sécurisés
query = "SELECT * FROM Customer WHERE Country = ?"
df = pd.read_sql(query, conn, params=("France",))
# Le ? est remplacé de manière sécurisée par sqlite3
```

#### 4. Utiliser des alias descriptifs

```sql
-- [X] MAUVAIS : colonnes sans alias
SELECT g.Name, COUNT(il.InvoiceLineId), SUM(il.UnitPrice * il.Quantity)
FROM InvoiceLine il JOIN Track t ON il.TrackId = t.TrackId
JOIN Genre g ON t.GenreId = g.GenreId GROUP BY g.Name;

-- [OK] BON : alias clairs
SELECT
    g.Name                                      AS genre,
    COUNT(il.InvoiceLineId)                     AS nb_ventes,
    ROUND(SUM(il.UnitPrice * il.Quantity), 2)   AS revenus_usd
FROM InvoiceLine il
    JOIN Track t ON il.TrackId = t.TrackId
    JOIN Genre g ON t.GenreId  = g.GenreId
GROUP BY g.Name;
```

### Bonnes pratiques Python

#### 5. Toujours utiliser le Context Manager pour les connexions

```python
# [X] MAUVAIS : connexion non fermée si erreur
conn = sqlite3.connect("chinook.db")
df = pd.read_sql("SELECT ...", conn)
conn.close()  # jamais atteint si pd.read_sql() lève une exception !

# [OK] BON : fermeture garantie
with DatabaseConnection() as conn:
    df = pd.read_sql("SELECT ...", conn)
# conn.close() est appelé automatiquement, même en cas d'erreur
```

#### 6. Centraliser les requêtes SQL dans `queries.py`

```python
# [X] MAUVAIS : SQL éparpillé dans tout le code
def analyser_ventes():
    with DatabaseConnection() as conn:
        df = pd.read_sql("SELECT ... FROM Invoice ...", conn)

def analyser_clients():
    with DatabaseConnection() as conn:
        df = pd.read_sql("SELECT ... FROM Customer ...", conn)

# [OK] BON : SQL centralisé
# Dans queries.py :
VENTES_PAR_PAYS = """
    SELECT BillingCountry, SUM(Total) FROM Invoice GROUP BY BillingCountry
"""
# Dans analysis.py :
from src.queries import VENTES_PAR_PAYS
df = pd.read_sql(VENTES_PAR_PAYS, conn)
```

---

## 10. Erreurs fréquentes

### Erreur 1 : `OperationalError: no such table`

```python
# ERREUR
df = pd.read_sql("SELECT * FROM customer", conn)
# -> sqlite3.OperationalError: no such table: customer

# CAUSE : SQLite est SENSIBLE À LA CASSE pour les noms de tables
# "customer" ≠ "Customer"

# SOLUTION
df = pd.read_sql("SELECT * FROM Customer", conn)  # C majuscule
```

### Erreur 2 : Connexion non fermée (fuite de ressources)

```python
# ERREUR
conn = sqlite3.connect("chinook.db")
# ... code qui lève une exception ...
conn.close()  # Jamais atteint -> ressource fugace

# SOLUTION : toujours utiliser with
with sqlite3.connect("chinook.db") as conn:
    pass  # conn.close() automatique
```

### Erreur 3 : Agrégation sans GROUP BY

```sql
-- ERREUR
SELECT Name, COUNT(*) FROM Genre;
-- -> SQLite retourne UN SEUL résultat (comportement non standard)
-- -> MySQL lèverait une erreur : column 'Name' non agrégée

-- SOLUTION : toujours GROUP BY avec COUNT/SUM/AVG
SELECT Name, COUNT(*) AS nb FROM Genre GROUP BY Name;
```

### Erreur 4 : Confusion NULL et 0

```sql
-- ERREUR : NULL != 0 en SQL
SELECT COUNT(*) FROM Customer WHERE State = NULL;
-- -> Retourne 0 même s'il y a des NULL dans State

-- SOLUTION : utiliser IS NULL / IS NOT NULL
SELECT COUNT(*) FROM Customer WHERE State IS NULL;
```

### Erreur 5 : `pd.read_sql` avec connexion fermée

```python
# ERREUR : on exécute la requête APRÈS la fermeture du with block
with DatabaseConnection() as conn:
    query = "SELECT * FROM Artist"
# conn est fermée ICI

df = pd.read_sql(query, conn)  # <- ERREUR : conn fermée !

# SOLUTION : tout faire DANS le with block
with DatabaseConnection() as conn:
    df = pd.read_sql(query, conn)  # <- OK
# df est disponible ici (c'est un DataFrame, pas une connexion)
```

---

## 11. Exercices

### [VERT] Exercice 1 — Premier contact SQL

**Mission** : Écris une requête SQL qui affiche :
- Le nom de l'artiste (`Artist.Name`)
- Le titre de l'album (`Album.Title`)
- Seulement pour les artistes dont le nom commence par "A"

Trie les résultats par nom d'artiste, puis par titre d'album.

**Indice** : Utilise `JOIN`, `WHERE`, `LIKE`, `ORDER BY`.

---

### [VERT] Exercice 2 — Compter les pistes

**Mission** : Pour chaque genre musical, affiche :
- Le nom du genre
- Le nombre de pistes dans ce genre
- Le prix moyen des pistes

Trie par nombre de pistes décroissant. Limite à 5 résultats.

**Indice** : `JOIN`, `GROUP BY`, `COUNT`, `AVG`, `ROUND`, `ORDER BY`, `LIMIT`.

---

### [JAUNE] Exercice 3 — Analyse des clients

**Mission** : Affiche la liste des pays avec au moins 2 clients,
avec pour chaque pays :
- Le nombre de clients
- Le nombre de factures total
- Le chiffre d'affaires total

Trie par chiffre d'affaires décroissant.

**Indice** : Jointure `Customer` <-> `Invoice`, `GROUP BY`, `HAVING`.

---

### [ROUGE] Exercice 4 — Hiérarchie des employés

**Mission** : Affiche la liste de tous les employés avec leur prénom,
leur nom, leur titre, ET le prénom et nom de leur manager.

Les employés sans manager (le PDG) affichent "N/A" pour le manager.

**Indice** : `LEFT JOIN` d'une table sur elle-même (auto-jointure),
`COALESCE` pour remplacer NULL.

---

## 12. Corrigés

### Corrigé Exercice 1

```sql
-- Artistes commençant par "A" avec leurs albums

SELECT
    ar.Name        AS artiste,  -- Nom de l'artiste (table Artist, alias ar)
    al.Title       AS album     -- Titre de l'album (table Album, alias al)
FROM Artist ar
    -- JOIN : combine Artist et Album sur ArtistId
    -- INNER JOIN = JOIN : ne garde que les lignes qui correspondent des deux côtés
    -- (artistes sans album ne sont PAS inclus)
    JOIN Album al ON ar.ArtistId = al.ArtistId
WHERE
    ar.Name LIKE 'A%'
    -- LIKE 'A%' : le nom commence par 'A'
    -- % est le joker "zéro ou plusieurs caractères"
    -- LIKE 'A%' : AC/DC, Accept, Aerosmith, Alanis...
    -- LIKE '%A%' : contient A n'importe où
    -- LIKE 'A_C%' : commence par A, suivi de 1 char, puis C
ORDER BY
    ar.Name ASC,   -- Tri par artiste (A->Z)
    al.Title ASC;  -- Puis par album (A->Z)

-- Résultat attendu (extrait) :
-- artiste              album
-- -------------------- ----------------------------------
-- AC/DC                For Those About To Rock (We Salute You)
-- AC/DC                Let There Be Rock
-- Accept               Balls to the Wall
-- Accept               Restless and Wild
-- Aerosmith            Big Ones
-- Alanis Morissette    Jagged Little Pill
-- ...
```

**Explication ligne par ligne** :
- `SELECT ar.Name AS artiste` : sélectionne la colonne Name de la table aliasée `ar`, renommée `artiste` dans le résultat
- `FROM Artist ar` : la table source est `Artist`, on lui donne l'alias `ar` pour ne pas répéter le nom complet
- `JOIN Album al ON ar.ArtistId = al.ArtistId` : on lie les deux tables par leur colonne commune `ArtistId`
- `WHERE ar.Name LIKE 'A%'` : filtre les artistes dont le nom commence par `A`
- `ORDER BY ar.Name ASC, al.Title ASC` : tri alphabétique, d'abord par artiste, puis par album

---

### Corrigé Exercice 2

```sql
-- Genres musicaux : nombre de pistes et prix moyen

SELECT
    g.Name                      AS genre,
    COUNT(t.TrackId)            AS nb_pistes,
    ROUND(AVG(t.UnitPrice), 2)  AS prix_moyen_usd

FROM Genre g
    -- LEFT JOIN : on garde TOUS les genres, même ceux sans piste
    -- (avec JOIN, un genre sans piste disparaîtrait)
    LEFT JOIN Track t ON g.GenreId = t.GenreId

GROUP BY
    g.GenreId,   -- Grouper par genre
    g.Name       -- Inclure Name car on l'utilise dans SELECT

    -- Note SQLite : GROUP BY g.GenreId suffit techniquement
    -- Mais bonne pratique : inclure toutes les colonnes non agrégées

ORDER BY nb_pistes DESC   -- Plus de pistes en premier

LIMIT 5;  -- Les 5 premiers seulement

-- Résultat attendu :
-- genre           nb_pistes  prix_moyen_usd
-- --------------- ---------  --------------
-- Rock                 1297            0.99
-- Latin                 579            0.99
-- Metal                 374            0.99
-- Alternative & Punk    332            0.99
-- Jazz                  130            0.99
```

**Note** : Tous les prix sont à $0.99 dans Chinook sauf quelques vidéos à $1.99.

---

### Corrigé Exercice 3

```sql
-- Pays avec au moins 2 clients, leurs factures et CA

SELECT
    c.Country                       AS pays,
    COUNT(DISTINCT c.CustomerId)    AS nb_clients,
    COUNT(i.InvoiceId)              AS nb_factures,
    ROUND(SUM(i.Total), 2)          AS ca_total_usd

FROM Customer c
    -- LEFT JOIN : garder les clients sans facture
    -- (pour voir les pays où des clients n'ont jamais acheté)
    LEFT JOIN Invoice i ON c.CustomerId = i.CustomerId

GROUP BY c.Country

HAVING COUNT(DISTINCT c.CustomerId) >= 2
-- HAVING filtre sur les résultats AGRÉGÉS (après GROUP BY)
-- WHERE filtre sur les données BRUTES (avant GROUP BY)
-- Règle : si la condition utilise COUNT/SUM/AVG -> HAVING
--         si elle utilise une colonne simple -> WHERE

ORDER BY ca_total_usd DESC;

-- Résultat attendu (extrait) :
-- pays           nb_clients  nb_factures  ca_total_usd
-- -------------- ----------  -----------  ------------
-- USA                    13           91        1040.49
-- Canada                  8           56         535.59
-- France                  5           35         389.07
-- Brazil                  5           35         427.68
-- Germany                 4           28         334.62
-- ...
```

---

### Corrigé Exercice 4

```sql
-- Hiérarchie des employés avec leur manager

SELECT
    -- Employé
    e.FirstName || ' ' || e.LastName   AS employe,
    -- || est l'opérateur de concaténation en SQLite
    -- En MySQL : CONCAT(e.FirstName, ' ', e.LastName)
    e.Title                            AS titre,

    -- Manager (peut être NULL pour le PDG)
    COALESCE(
        m.FirstName || ' ' || m.LastName,
        'N/A (Direction Générale)'
    )                                  AS manager
    -- COALESCE(val1, val2) : retourne val1 si non NULL, sinon val2
    -- Ici : si le manager existe -> son nom, sinon -> "N/A..."

FROM Employee e
    -- LEFT JOIN sur la même table avec un alias différent
    -- e = l'employé (ligne principale)
    -- m = le manager (la même table, mais la ligne du manager)
    LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId
    -- ON e.ReportsTo = m.EmployeeId :
    -- e.ReportsTo est l'ID du manager de l'employé e
    -- m.EmployeeId est l'ID de la ligne du manager
    -- LEFT JOIN : si ReportsTo est NULL (PDG), on garde quand même l'employé

ORDER BY e.EmployeeId;

-- Résultat attendu :
-- employe              titre                      manager
-- -------------------- -------------------------- -----------------------
-- Andrew Adams         General Manager            N/A (Direction Générale)
-- Nancy Edwards        Sales Manager              Andrew Adams
-- Jane Peacock         Sales Support Agent        Nancy Edwards
-- Margaret Park        Sales Support Agent        Nancy Edwards
-- Steve Johnson        Sales Support Agent        Nancy Edwards
-- Michael Mitchell     IT Manager                 Andrew Adams
-- Robert King          IT Staff                   Michael Mitchell
-- Laura Callahan       IT Staff                   Michael Mitchell
```

---

## 13. Récapitulatif

### Ce que tu as appris dans la Partie 1

| Concept | Acquis |
|---------|--------|
| Structure d'un projet Python professionnel | [OK] |
| Connexion SQLite avec Context Manager | [OK] |
| Schéma relationnel Chinook (11 tables) | [OK] |
| Clés primaires (PK) et étrangères (FK) | [OK] |
| Relations 1:N et N:N (table de liaison) | [OK] |
| Auto-référence (Employee.ReportsTo) | [OK] |
| SELECT, WHERE, JOIN, GROUP BY, HAVING | [OK] |
| LIKE, COALESCE, ORDER BY, LIMIT | [OK] |
| Injection SQL et paramètres sécurisés | [OK] |
| Logging Python professionnel | [OK] |

### Fichiers créés

```
datainsight_sql/
├── data/chinook.db        <- Base téléchargée (11 tables, ~15 000 lignes)
├── src/__init__.py
├── src/db_connection.py   <- DatabaseConnection (Context Manager)
├── src/utils.py           <- timeit, configurer_logging, constantes
├── main.py                <- CLI : info, explore, query
└── requirements.txt
```

### Prochaine étape

**Partie 2 : Requêtes SQL de Base et Intermédiaires**
- `SELECT` avec toutes ses clauses
- Fonctions d'agrégation (COUNT, SUM, AVG, MIN, MAX)
- Filtres avancés (BETWEEN, IN, EXISTS)
- Fonctions de chaîne et de date SQLite
- Sous-requêtes scalaires et corrélées

---

*DataInsight Pro SQL Edition — Partie 1/8 | Base : Chinook Music Database (lerocha/chinook-database)*

# DataInsight Pro — SQL Edition
# Partie 2 : Requêtes SQL de Base et Intermédiaires

---

> **Contexte** : Tu es Data Analyst chez Chinook Music. Ton manager t'a demandé
> de répondre à une série de questions business en SQL avant le comité de direction
> de vendredi. Dans cette partie, tu maîtrises `SELECT` complet, les agrégations,
> les filtres avancés et les fonctions SQL essentielles.

---

## Table des matières

1. [Contexte métier](#1-contexte-métier)
2. [Objectifs pédagogiques](#2-objectifs-pédagogiques)
3. [Théorie : anatomie d'un SELECT complet](#3-théorie--anatomie-dun-select-complet)
4. [Code : `src/queries.py`](#4-code--srcqueriespy)
5. [Code : `src/data_loader.py`](#5-code--srcdata_loaderpy)
6. [Requêtes métier commentées](#6-requêtes-métier-commentées)
7. [Fonctions SQL essentielles](#7-fonctions-sql-essentielles)
8. [Bonnes pratiques](#8-bonnes-pratiques)
9. [Erreurs fréquentes](#9-erreurs-fréquentes)
10. [Exercices](#10-exercices)
11. [Corrigés](#11-corrigés)
12. [Récapitulatif](#12-récapitulatif)

---

## 1. Contexte métier

Le directeur commercial de Chinook veut des réponses à ces 8 questions pour vendredi :

1. Quel est le **top 10 des artistes** par nombre d'albums ?
2. Quels sont les **genres les plus vendus** en revenus ?
3. Quel est le **panier moyen** par pays ?
4. Quels clients ont **dépensé plus de $40** au total ?
5. Quels employés ont le **plus de clients assignés** ?
6. Quelle est la **durée moyenne** des pistes par genre ?
7. Y a-t-il des **clients sans facture** (clients inactifs) ?
8. Quel est le **mois qui génère le plus de revenus** ?

---

## 2. Objectifs pédagogiques

| Concept SQL | Couverture |
|-------------|-----------|
| `SELECT` complet (toutes les clauses) | [OK] |
| `WHERE` avec opérateurs (=, >, <, BETWEEN, IN, LIKE, IS NULL) | [OK] |
| Fonctions d'agrégation (COUNT, SUM, AVG, MIN, MAX) | [OK] |
| `GROUP BY` + `HAVING` | [OK] |
| `ORDER BY` + `LIMIT` + `OFFSET` | [OK] |
| Fonctions de texte (UPPER, LOWER, SUBSTR, LENGTH, TRIM) | [OK] |
| Fonctions de date (strftime en SQLite) | [OK] |
| Fonctions numériques (ROUND, ABS, CAST) | [OK] |
| Sous-requêtes scalaires | [OK] |
| `CASE WHEN` | [OK] |

---

## 3. Théorie : anatomie d'un SELECT complet

### Ordre d'écriture vs ordre d'exécution

C'est l'une des confusions les plus courantes chez les débutants :
l'ordre dans lequel tu **écris** les clauses SQL ≠ l'ordre dans lequel
le moteur les **exécute**.

```sql
-- ORDRE D'ÉCRITURE (ce que tu tapes)
SELECT    colonne1, AGG(colonne2)    -- 5. Résultats calculés
FROM      table                      -- 1. Source de données
JOIN      autre_table ON condition   -- 2. Jointures
WHERE     condition_filtre           -- 3. Filtre lignes
GROUP BY  colonne1                   -- 4. Regroupement
HAVING    condition_agregee          -- 5. Filtre groupes
ORDER BY  colonne1                   -- 6. Tri
LIMIT     n;                         -- 7. Limitation

-- ORDRE D'EXÉCUTION (ce que le moteur fait)
-- 1. FROM     -> Quelles tables ?
-- 2. JOIN     -> Combine les tables
-- 3. WHERE    -> Filtre les lignes (AVANT agrégation)
-- 4. GROUP BY -> Regroupe les lignes
-- 5. HAVING   -> Filtre les groupes (APRÈS agrégation)
-- 6. SELECT   -> Calcule les colonnes du résultat
-- 7. ORDER BY -> Trie le résultat
-- 8. LIMIT    -> Coupe le résultat
```

**Pourquoi c'est important ?**

```sql
-- ERREUR courante : utiliser un alias de SELECT dans WHERE
SELECT
    Country,
    SUM(Total) AS ca_total
FROM Invoice
WHERE ca_total > 100    -- [X] ERREUR : ca_total n'existe pas encore au moment du WHERE
GROUP BY Country;

-- SOLUTION : utiliser HAVING (exécuté après SELECT)
SELECT
    Country,
    SUM(Total) AS ca_total
FROM Invoice
GROUP BY Country
HAVING SUM(Total) > 100;  -- [OK] OK : HAVING est exécuté après l'agrégation
```

---

## 4. Code : `src/queries.py`

```python
"""
queries.py — Centralisation de toutes les requêtes SQL

PRINCIPE FONDAMENTAL : le code SQL ne doit JAMAIS être éparpillé
dans les autres modules Python. Il doit être centralisé ici pour :
- Faciliter la maintenance (modifier une requête = un seul endroit)
- Permettre les revues de code (un expert SQL révise ce fichier)
- Versionner les requêtes (Git montre ce qui a changé)
- Réutiliser les requêtes dans plusieurs endroits

Convention de nommage :
- Constantes MAJUSCULES pour les requêtes simples
- Fonctions pour les requêtes paramétrées (avec WHERE dynamique)
"""


# ============================================================
# SECTION 1 : REQUÊTES DE RÉFÉRENCE (référentiels)
# ============================================================

# --- Artistes ---
TOUS_LES_ARTISTES = """
    SELECT
        ArtistId,
        Name    AS artiste
    FROM Artist
    ORDER BY Name
"""
# Explications :
# SELECT ArtistId, Name AS artiste  : 2 colonnes, Name renommée en artiste
# FROM Artist                       : table source
# ORDER BY Name                     : tri alphabétique

ARTISTES_AVEC_NB_ALBUMS = """
    SELECT
        ar.ArtistId,
        ar.Name                     AS artiste,
        COUNT(al.AlbumId)           AS nb_albums
    FROM Artist ar
        LEFT JOIN Album al ON ar.ArtistId = al.ArtistId
        -- LEFT JOIN : garder les artistes SANS album (nb_albums = 0)
        -- INNER JOIN : ne garderait que les artistes AVEC album
    GROUP BY
        ar.ArtistId,
        ar.Name
    ORDER BY nb_albums DESC
"""

TOP_10_ARTISTES = """
    SELECT
        ar.Name                     AS artiste,
        COUNT(al.AlbumId)           AS nb_albums
    FROM Artist ar
        LEFT JOIN Album al ON ar.ArtistId = al.ArtistId
    GROUP BY ar.ArtistId, ar.Name
    ORDER BY nb_albums DESC
    LIMIT 10
    -- LIMIT 10 : retourne au plus 10 lignes
"""

# --- Genres ---
GENRES_PAR_REVENUS = """
    SELECT
        g.Name                                      AS genre,
        COUNT(il.InvoiceLineId)                     AS nb_ventes,
        ROUND(SUM(il.UnitPrice * il.Quantity), 2)   AS revenus_usd,
        ROUND(AVG(il.UnitPrice), 2)                 AS prix_moyen
    FROM Genre g
        LEFT JOIN Track t       ON g.GenreId    = t.GenreId
        LEFT JOIN InvoiceLine il ON t.TrackId   = il.TrackId
        -- Chaîne de jointures : Genre -> Track -> InvoiceLine
        -- On remonte depuis le genre jusqu'aux ventes
    GROUP BY
        g.GenreId,
        g.Name
    HAVING COUNT(il.InvoiceLineId) > 0
        -- HAVING : ne garder que les genres qui ont des ventes
    ORDER BY revenus_usd DESC
"""

DUREE_MOYENNE_PAR_GENRE = """
    SELECT
        g.Name                                          AS genre,
        COUNT(t.TrackId)                                AS nb_pistes,
        ROUND(AVG(t.Milliseconds) / 1000.0 / 60.0, 2)  AS duree_moy_min,
        -- Milliseconds / 1000.0 -> secondes (1.0 force la division décimale)
        -- secondes / 60.0       -> minutes
        -- ROUND(..., 2)         -> 2 décimales
        ROUND(MIN(t.Milliseconds) / 1000.0 / 60.0, 2)  AS duree_min_min,
        ROUND(MAX(t.Milliseconds) / 1000.0 / 60.0, 2)  AS duree_max_min
    FROM Genre g
        JOIN Track t ON g.GenreId = t.GenreId
    GROUP BY g.GenreId, g.Name
    ORDER BY duree_moy_min DESC
"""


# ============================================================
# SECTION 2 : REQUÊTES CLIENTS
# ============================================================

TOUS_LES_CLIENTS = """
    SELECT
        c.CustomerId,
        c.FirstName || ' ' || c.LastName    AS client_nom,
        -- || : opérateur de concaténation SQLite
        -- MySQL utiliserait : CONCAT(c.FirstName, ' ', c.LastName)
        c.Country,
        c.Email,
        e.FirstName || ' ' || e.LastName    AS agent_support
    FROM Customer c
        LEFT JOIN Employee e ON c.SupportRepId = e.EmployeeId
        -- LEFT JOIN : afficher les clients même sans agent assigné
    ORDER BY c.Country, c.LastName
"""

CLIENTS_INACTIFS = """
    SELECT
        c.CustomerId,
        c.FirstName || ' ' || c.LastName    AS client_nom,
        c.Country,
        c.Email
    FROM Customer c
        LEFT JOIN Invoice i ON c.CustomerId = i.CustomerId
        -- LEFT JOIN : garder les clients MÊME s'ils n'ont pas de facture
    WHERE i.InvoiceId IS NULL
        -- IS NULL : la jointure n'a rien trouvé -> pas de facture pour ce client
        -- Astuce : après LEFT JOIN, si le côté droit est NULL -> pas de correspondance
    ORDER BY c.Country
"""

VALEUR_VIE_CLIENT = """
    SELECT
        c.CustomerId,
        c.FirstName || ' ' || c.LastName        AS client_nom,
        c.Country,
        COUNT(i.InvoiceId)                       AS nb_achats,
        ROUND(SUM(i.Total), 2)                   AS ltv_usd,
        -- LTV = Lifetime Value = valeur totale dépensée par le client
        ROUND(AVG(i.Total), 2)                   AS panier_moyen,
        MIN(i.InvoiceDate)                       AS premier_achat,
        MAX(i.InvoiceDate)                       AS dernier_achat
    FROM Customer c
        JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    ORDER BY ltv_usd DESC
"""

CLIENTS_PAR_SEUIL = """
    SELECT
        c.FirstName || ' ' || c.LastName    AS client_nom,
        c.Country,
        ROUND(SUM(i.Total), 2)              AS total_depense
    FROM Customer c
        JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    HAVING SUM(i.Total) > :seuil
        -- :seuil est un paramètre nommé (syntaxe SQLAlchemy/Python)
        -- On utilisera params={"seuil": 40} lors de l'exécution
    ORDER BY total_depense DESC
"""


# ============================================================
# SECTION 3 : REQUÊTES FINANCIÈRES
# ============================================================

CA_PAR_PAYS = """
    SELECT
        BillingCountry                      AS pays,
        COUNT(InvoiceId)                    AS nb_factures,
        ROUND(SUM(Total), 2)                AS ca_total,
        ROUND(AVG(Total), 2)                AS panier_moyen,
        ROUND(MIN(Total), 2)                AS facture_min,
        ROUND(MAX(Total), 2)                AS facture_max
    FROM Invoice
    GROUP BY BillingCountry
    ORDER BY ca_total DESC
"""

CA_PAR_MOIS = """
    SELECT
        strftime('%Y-%m', InvoiceDate)      AS mois,
        -- strftime : fonction de formatage de date SQLite
        -- '%Y-%m' : format YYYY-MM (ex: 2013-01)
        -- MySQL : DATE_FORMAT(InvoiceDate, '%Y-%m')
        -- PostgreSQL : TO_CHAR(InvoiceDate, 'YYYY-MM')
        COUNT(InvoiceId)                    AS nb_factures,
        ROUND(SUM(Total), 2)                AS ca_mensuel,
        ROUND(AVG(Total), 2)                AS panier_moyen
    FROM Invoice
    GROUP BY strftime('%Y-%m', InvoiceDate)
    ORDER BY mois ASC
"""

CA_PAR_TRIMESTRE = """
    SELECT
        strftime('%Y', InvoiceDate)         AS annee,
        -- '%Y' : juste l'année (ex: 2013)
        CASE
            WHEN CAST(strftime('%m', InvoiceDate) AS INTEGER) BETWEEN 1 AND 3  THEN 'Q1'
            WHEN CAST(strftime('%m', InvoiceDate) AS INTEGER) BETWEEN 4 AND 6  THEN 'Q2'
            WHEN CAST(strftime('%m', InvoiceDate) AS INTEGER) BETWEEN 7 AND 9  THEN 'Q3'
            ELSE 'Q4'
        END                                 AS trimestre,
        -- CASE WHEN : équivalent SQL de if/elif/else
        -- CAST(... AS INTEGER) : convertit le texte '01' en entier 1
        ROUND(SUM(Total), 2)                AS ca_trimestre
    FROM Invoice
    GROUP BY annee, trimestre
    ORDER BY annee, trimestre
"""


# ============================================================
# SECTION 4 : REQUÊTES EMPLOYÉS
# ============================================================

PERFORMANCE_AGENTS = """
    SELECT
        e.FirstName || ' ' || e.LastName    AS agent,
        e.Title,
        COUNT(DISTINCT c.CustomerId)        AS nb_clients,
        -- COUNT(DISTINCT ...) : compter les valeurs UNIQUES
        -- évite de compter un client plusieurs fois s'il a plusieurs factures
        COUNT(i.InvoiceId)                  AS nb_ventes,
        ROUND(SUM(i.Total), 2)              AS ca_genere,
        ROUND(AVG(i.Total), 2)              AS panier_moyen
    FROM Employee e
        LEFT JOIN Customer c    ON e.EmployeeId     = c.SupportRepId
        LEFT JOIN Invoice i     ON c.CustomerId     = i.CustomerId
    WHERE e.Title LIKE '%Sales%'
        -- Filtre les employés dont le titre contient "Sales"
        -- Cela exclut le PDG, les IT, etc.
    GROUP BY e.EmployeeId, e.FirstName, e.LastName, e.Title
    ORDER BY ca_genere DESC
"""

HIERARCHIE_EMPLOYES = """
    SELECT
        e.EmployeeId,
        e.FirstName || ' ' || e.LastName            AS employe,
        e.Title,
        COALESCE(
            m.FirstName || ' ' || m.LastName,
            '— (Direction Générale)'
        )                                           AS manager,
        -- COALESCE(val1, val2) : retourne val1 si non NULL, sinon val2
        e.HireDate
    FROM Employee e
        LEFT JOIN Employee m ON e.ReportsTo = m.EmployeeId
        -- Auto-jointure : Employee se joint à lui-même
        -- e = l'employé, m = son manager (même table, autre ligne)
    ORDER BY e.EmployeeId
"""


# ============================================================
# SECTION 5 : REQUÊTES AVEC CASE WHEN
# ============================================================

CLASSEMENT_CLIENTS = """
    SELECT
        c.FirstName || ' ' || c.LastName    AS client,
        c.Country,
        ROUND(SUM(i.Total), 2)              AS total_achats,
        CASE
            WHEN SUM(i.Total) >= 40 THEN 'Gold'
            WHEN SUM(i.Total) >= 20 THEN 'Silver'
            ELSE                         'Bronze'
        END                                 AS segment_client
        -- CASE WHEN : attribue un label selon la valeur de SUM(i.Total)
        -- Gold   : >= $40
        -- Silver : $20 à $39.99
        -- Bronze : < $20
    FROM Customer c
        JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    ORDER BY total_achats DESC
"""

ANALYSE_PRIX_PISTES = """
    SELECT
        Name                                AS piste,
        UnitPrice,
        CASE
            WHEN UnitPrice = 0.99  THEN 'Standard ($0.99)'
            WHEN UnitPrice = 1.99  THEN 'Vidéo ($1.99)'
            ELSE                        'Autre prix'
        END                                 AS categorie_prix,
        ROUND(Milliseconds / 1000.0 / 60.0, 2) AS duree_min
    FROM Track
    ORDER BY UnitPrice DESC, duree_min DESC
    LIMIT 20
"""


# ============================================================
# SECTION 6 : SOUS-REQUÊTES
# ============================================================

CLIENTS_AU_DESSUS_MOYENNE = """
    SELECT
        c.FirstName || ' ' || c.LastName    AS client,
        c.Country,
        ROUND(SUM(i.Total), 2)              AS total_achats
    FROM Customer c
        JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    HAVING SUM(i.Total) > (
        -- Sous-requête scalaire : retourne UNE seule valeur
        -- Cette valeur est calculée une seule fois et réutilisée
        SELECT AVG(total_par_client)
        FROM (
            -- Sous-requête dans la sous-requête (sous-requête imbriquée)
            SELECT SUM(Total) AS total_par_client
            FROM Invoice
            GROUP BY CustomerId
        )
    )
    ORDER BY total_achats DESC
"""

PISTES_PLUS_VENDUES = """
    SELECT
        t.Name                              AS piste,
        ar.Name                             AS artiste,
        COUNT(il.InvoiceLineId)             AS nb_ventes,
        ROUND(SUM(il.UnitPrice), 2)         AS revenus
    FROM Track t
        JOIN InvoiceLine il ON t.TrackId    = il.TrackId
        JOIN Album al       ON t.AlbumId    = al.AlbumId
        JOIN Artist ar      ON al.ArtistId  = ar.ArtistId
    GROUP BY t.TrackId, t.Name, ar.Name
    HAVING COUNT(il.InvoiceLineId) = (
        -- Sous-requête scalaire : le maximum de ventes
        SELECT MAX(nb_ventes)
        FROM (
            SELECT COUNT(InvoiceLineId) AS nb_ventes
            FROM InvoiceLine
            GROUP BY TrackId
        )
    )
"""
```

---

## 5. Code : `src/data_loader.py`

```python
"""
data_loader.py — Interface entre SQL et pandas

Ce module fournit des fonctions de haut niveau pour charger les données.
Chaque fonction :
1. Utilise une requête de queries.py
2. L'exécute via DatabaseConnection
3. Retourne un DataFrame pandas propre
"""

import pandas as pd
import logging
from pathlib import Path
import sys
sys.path.insert(0, str(Path(__file__).parent.parent))

from src.db_connection import DatabaseConnection, executer_requete
from src.utils import timeit
from src import queries

logger = logging.getLogger(__name__)


# ============================================================
# CHARGEMENT DES RÉFÉRENTIELS
# ============================================================

@timeit
def charger_artistes(avec_albums: bool = False) -> pd.DataFrame:
    """
    Charge la liste des artistes depuis la base.

    Args:
        avec_albums (bool) : Si True, inclut le nombre d'albums par artiste.

    Returns:
        pd.DataFrame : Artistes avec colonnes ArtistId, artiste (+ nb_albums si demandé)
    """
    requete = (queries.ARTISTES_AVEC_NB_ALBUMS
               if avec_albums
               else queries.TOUS_LES_ARTISTES)

    with DatabaseConnection() as conn:
        # pd.read_sql() : exécute la requête et retourne un DataFrame
        # conn : la connexion SQLite (sqlite3.Connection)
        df = pd.read_sql(requete, conn)

    logger.info(f"Artistes chargés : {len(df):,} lignes")
    return df


@timeit
def charger_genres() -> pd.DataFrame:
    """Charge les genres avec leurs revenus et nombre de ventes."""
    with DatabaseConnection() as conn:
        df = pd.read_sql(queries.GENRES_PAR_REVENUS, conn)
    logger.info(f"Genres chargés : {len(df)} genres")
    return df


# ============================================================
# CHARGEMENT DES DONNÉES CLIENTS
# ============================================================

@timeit
def charger_clients(inactifs_seulement: bool = False) -> pd.DataFrame:
    """
    Charge la liste des clients.

    Args:
        inactifs_seulement (bool) : Si True, ne charge que les clients sans facture.

    Returns:
        pd.DataFrame : Clients avec agent support et statistiques
    """
    requete = (queries.CLIENTS_INACTIFS
               if inactifs_seulement
               else queries.TOUS_LES_CLIENTS)

    with DatabaseConnection() as conn:
        df = pd.read_sql(requete, conn)

    logger.info(
        f"Clients {'inactifs' if inactifs_seulement else 'tous'} "
        f"chargés : {len(df)}"
    )
    return df


@timeit
def charger_ltv_clients() -> pd.DataFrame:
    """
    Charge la Lifetime Value (LTV) de chaque client.

    Returns:
        pd.DataFrame : Clients triés par LTV décroissante avec :
            client_nom, country, nb_achats, ltv_usd, panier_moyen,
            premier_achat, dernier_achat
    """
    with DatabaseConnection() as conn:
        df = pd.read_sql(queries.VALEUR_VIE_CLIENT, conn)

    # Conversion des colonnes de dates en datetime pandas
    # pd.to_datetime() : convertit une colonne string en datetime
    for col in ["premier_achat", "dernier_achat"]:
        if col in df.columns:
            df[col] = pd.to_datetime(df[col])
            # errors="coerce" : met NaT si la conversion échoue
            # (utile si certaines lignes ont des formats de date incorrects)

    logger.info(f"LTV clients chargée : {len(df)} clients")
    return df


@timeit
def charger_clients_par_seuil(seuil: float = 40.0) -> pd.DataFrame:
    """
    Charge les clients ayant dépensé plus d'un certain montant.

    Args:
        seuil (float) : Montant minimum total dépensé (défaut $40)

    Returns:
        pd.DataFrame : Clients au-dessus du seuil
    """
    with DatabaseConnection() as conn:
        # Requête avec paramètre nommé :seuil
        # params={"seuil": seuil} injecte la valeur de manière sécurisée
        df = pd.read_sql(
            queries.CLIENTS_PAR_SEUIL,
            conn,
            params={"seuil": seuil}
        )
    logger.info(f"Clients > ${seuil:.2f} : {len(df)} trouvés")
    return df


# ============================================================
# CHARGEMENT DES DONNÉES FINANCIÈRES
# ============================================================

@timeit
def charger_ca_pays() -> pd.DataFrame:
    """Charge le CA par pays avec métriques financières."""
    with DatabaseConnection() as conn:
        df = pd.read_sql(queries.CA_PAR_PAYS, conn)
    return df


@timeit
def charger_ca_mensuel() -> pd.DataFrame:
    """
    Charge l'évolution mensuelle du CA.

    Returns:
        pd.DataFrame : CA par mois avec colonnes mois (YYYY-MM),
            nb_factures, ca_mensuel, panier_moyen
    """
    with DatabaseConnection() as conn:
        df = pd.read_sql(queries.CA_PAR_MOIS, conn)

    # Conversion de la colonne mois en datetime pour faciliter les graphiques
    # format="%Y-%m" indique le format de parsing
    df["mois_dt"] = pd.to_datetime(df["mois"], format="%Y-%m")
    # On garde la colonne mois (string) pour l'affichage
    # et on ajoute mois_dt (datetime) pour les graphiques temporels

    return df


# ============================================================
# CHARGEMENT DES DONNÉES EMPLOYÉS
# ============================================================

@timeit
def charger_performance_agents() -> pd.DataFrame:
    """Charge les métriques de performance des agents commerciaux."""
    with DatabaseConnection() as conn:
        df = pd.read_sql(queries.PERFORMANCE_AGENTS, conn)
    return df


# ============================================================
# FONCTIONS UTILITAIRES
# ============================================================

@timeit
def apercu_complet() -> dict:
    """
    Charge un aperçu de toutes les tables principales.

    Returns:
        dict : Dictionnaire {nom_table: DataFrame}

    Example:
        >>> data = apercu_complet()
        >>> print(data["artistes"].head())
    """
    return {
        "artistes":  charger_artistes(avec_albums=True),
        "genres":    charger_genres(),
        "clients":   charger_ltv_clients(),
        "ca_pays":   charger_ca_pays(),
        "ca_mois":   charger_ca_mensuel(),
        "agents":    charger_performance_agents(),
    }
```

---

## 6. Requêtes métier commentées

### Requête 1 : Top 10 artistes par nombre d'albums

```sql
-- CONTEXTE : Le directeur marketing veut savoir quels artistes ont
-- le catalogue le plus riche (pour négocier de nouveaux droits similaires)

SELECT
    ar.Name                     AS artiste,
    COUNT(al.AlbumId)           AS nb_albums,
    -- COUNT(al.AlbumId) : compte les AlbumId non NULL dans chaque groupe
    -- Pourquoi pas COUNT(*) ? COUNT(*) compterait même les artistes sans album
    -- COUNT(al.AlbumId) retourne 0 pour les artistes sans album (avec LEFT JOIN)

    GROUP_CONCAT(al.Title, ' | ')  AS albums_liste
    -- GROUP_CONCAT() : concatène les valeurs d'un groupe
    -- Séparateur : ' | '
    -- Résultat : "Album1 | Album2 | Album3"
    -- MySQL/PostgreSQL : GROUP_CONCAT() ou STRING_AGG()

FROM Artist ar
    LEFT JOIN Album al ON ar.ArtistId = al.ArtistId
    -- LEFT JOIN pour inclure les artistes sans album (Iron Maiden a 21 albums)

GROUP BY ar.ArtistId, ar.Name
    -- GROUP BY sur la PK (ArtistId) + la colonne affichée (Name)

ORDER BY nb_albums DESC
LIMIT 10;

-- RÉSULTAT ATTENDU :
-- artiste                  nb_albums albums_liste
-- ----------------------- --------- ------------------------------------------
-- Iron Maiden                    21  A Matter of Life...| Brave New World | ...
-- Led Zeppelin                   14  ...
-- Deep Purple                    11  ...
-- Metallica                      10  ...
-- U2                             10  ...
```

---

### Requête 2 : Revenus par genre avec part de marché

```sql
-- CONTEXTE : Identifier les genres à fort potentiel pour orienter
-- la stratégie d'acquisition de contenu

SELECT
    g.Name                                          AS genre,
    COUNT(il.InvoiceLineId)                         AS nb_ventes,
    ROUND(SUM(il.UnitPrice * il.Quantity), 2)       AS revenus_usd,

    -- Calcul de la part de marché (%)
    ROUND(
        SUM(il.UnitPrice * il.Quantity) * 100.0
        / SUM(SUM(il.UnitPrice * il.Quantity)) OVER(),
        -- SUM(...) OVER() : fonction de fenêtre (WINDOW FUNCTION)
        -- Calcule la SUM sur TOUTE la table (pas juste le groupe)
        -- Cela permet de diviser par le total pour avoir le %
        2
    )                                               AS part_marche_pct

FROM Genre g
    JOIN Track t        ON g.GenreId    = t.GenreId
    JOIN InvoiceLine il ON t.TrackId    = il.TrackId
GROUP BY g.GenreId, g.Name
ORDER BY revenus_usd DESC;

-- Note : SUM(...) OVER() est une Window Function
-- Elle est disponible dans SQLite 3.25+ (2018)
-- Si problème : utiliser une sous-requête pour le total
```

---

### Requête 3 : Analyse de cohort — achats par mois

```sql
-- CONTEXTE : Comprendre la saisonnalité des ventes

SELECT
    strftime('%Y', InvoiceDate)     AS annee,
    -- strftime('%Y', date) : extrait l'année (ex: '2013')

    strftime('%m', InvoiceDate)     AS mois_num,
    -- strftime('%m', date) : extrait le mois sur 2 chiffres (ex: '01')

    CASE strftime('%m', InvoiceDate)
        WHEN '01' THEN 'Janvier'
        WHEN '02' THEN 'Février'
        WHEN '03' THEN 'Mars'
        WHEN '04' THEN 'Avril'
        WHEN '05' THEN 'Mai'
        WHEN '06' THEN 'Juin'
        WHEN '07' THEN 'Juillet'
        WHEN '08' THEN 'Août'
        WHEN '09' THEN 'Septembre'
        WHEN '10' THEN 'Octobre'
        WHEN '11' THEN 'Novembre'
        WHEN '12' THEN 'Décembre'
    END                             AS mois_nom,
    -- CASE colonne WHEN val THEN résultat... END
    -- Variante du CASE WHEN pour comparer une colonne à des valeurs fixes

    COUNT(InvoiceId)                AS nb_factures,
    ROUND(SUM(Total), 2)            AS ca_mensuel

FROM Invoice
GROUP BY annee, mois_num, mois_nom
ORDER BY annee, mois_num;
```

---

## 7. Fonctions SQL essentielles

### 7.1 Fonctions de texte (SQLite)

```sql
-- UPPER / LOWER : Conversion de casse
SELECT
    UPPER('hello world'),   -- -> 'HELLO WORLD'
    LOWER('HELLO WORLD');   -- -> 'hello world'

-- Exemple pratique : recherche insensible à la casse
SELECT * FROM Artist
WHERE LOWER(Name) LIKE '%iron maiden%';
-- Trouve "Iron Maiden", "IRON MAIDEN", "iron maiden"

-- LENGTH : Longueur d'une chaîne
SELECT Name, LENGTH(Name) AS longueur
FROM Artist
WHERE LENGTH(Name) > 20
ORDER BY longueur DESC;

-- SUBSTR : Sous-chaîne
-- SUBSTR(chaîne, début, longueur)
-- Début commence à 1 (pas 0 !)
SELECT
    Name,
    SUBSTR(Name, 1, 10)     AS 10_premiers_chars,
    SUBSTR(Name, -3)        AS 3_derniers_chars
    -- Indice négatif : compte depuis la fin
FROM Artist;

-- TRIM : Supprime les espaces (ou autres caractères)
SELECT
    TRIM('  hello  '),          -- -> 'hello'
    LTRIM('  hello  '),         -- -> 'hello  ' (Left Trim)
    RTRIM('  hello  '),         -- -> '  hello' (Right Trim)
    TRIM('xxhelloxx', 'x');     -- -> 'hello' (caractère personnalisé)

-- REPLACE : Remplacement de sous-chaîne
SELECT REPLACE('hello world', 'world', 'SQL');
-- -> 'hello SQL'

-- INSTR : Position d'une sous-chaîne (0 si absent)
SELECT Name, INSTR(Name, 'The') AS position_the
FROM Artist
WHERE INSTR(Name, 'The') > 0;
```

### 7.2 Fonctions de date (SQLite)

```sql
-- strftime : formatage de date (fonction principale SQLite)
-- Format MySQL  : DATE_FORMAT(date, '%Y-%m-%d')
-- Format PostgreSQL : TO_CHAR(date, 'YYYY-MM-DD')

SELECT
    InvoiceDate,
    strftime('%Y', InvoiceDate)             AS annee,    -- '2013'
    strftime('%m', InvoiceDate)             AS mois,     -- '01'
    strftime('%d', InvoiceDate)             AS jour,     -- '15'
    strftime('%Y-%m', InvoiceDate)          AS annee_mois, -- '2013-01'
    strftime('%Y-%m-%d', InvoiceDate)       AS date_iso,  -- '2013-01-15'
    strftime('%H:%M', InvoiceDate)          AS heure      -- '00:00' (si stocké)
FROM Invoice
LIMIT 5;

-- DATE ARITHMÉTIQUE en SQLite
SELECT
    InvoiceDate,
    -- Ajouter des jours
    date(InvoiceDate, '+30 days')           AS plus_30_jours,
    -- Soustraire des mois
    date(InvoiceDate, '-3 months')          AS moins_3_mois,
    -- Différence entre deux dates
    CAST(
        julianday('now') - julianday(InvoiceDate)
        AS INTEGER
    )                                       AS jours_ecoul
    -- julianday() : convertit une date en nombre de jours depuis -4714-11-24
    -- La différence donne le nombre de jours entre les deux dates
FROM Invoice
LIMIT 5;
```

### 7.3 Fonctions numériques

```sql
-- ROUND : Arrondir
SELECT
    ROUND(3.14159, 2),  -- -> 3.14
    ROUND(3.5),          -- -> 4 (arrondi au plus proche)
    ROUND(3.55, 1);      -- -> 3.6

-- ABS : Valeur absolue
SELECT ABS(-42);  -- -> 42

-- CAST : Conversion de type
SELECT
    CAST('42' AS INTEGER),       -- -> 42 (string -> entier)
    CAST(42 AS TEXT),            -- -> '42' (entier -> string)
    CAST(42.7 AS INTEGER),       -- -> 42 (décimal -> entier, troncature)
    CAST('01' AS INTEGER);       -- -> 1 (string avec zéro initial)

-- MIN / MAX : Sur colonnes numériques, dates, strings
SELECT
    MIN(Total), MAX(Total), AVG(Total), SUM(Total)
FROM Invoice;

-- NULLIF : Retourne NULL si deux valeurs sont égales (évite division par zéro)
SELECT
    SUM(Total) / NULLIF(COUNT(InvoiceId), 0) AS moyenne_securisee
FROM Invoice WHERE 1=0;
-- Si COUNT = 0 (pas de lignes), NULLIF retourne NULL -> pas de division par zéro
```

---

## 8. Bonnes pratiques

### Utiliser des CTEs (Common Table Expressions)

```sql
-- CTE = sous-requête nommée au début de la requête
-- WITH nom_cte AS (...) SELECT ... FROM nom_cte

-- Exemple : top artistes vs moyenne générale
WITH
    ventes_par_genre AS (
        -- CTE 1 : revenus par genre
        SELECT
            g.Name              AS genre,
            SUM(il.UnitPrice)   AS revenus
        FROM Genre g
            JOIN Track t        ON g.GenreId  = t.GenreId
            JOIN InvoiceLine il ON t.TrackId  = il.TrackId
        GROUP BY g.GenreId, g.Name
    ),
    moyenne_globale AS (
        -- CTE 2 : moyenne des revenus par genre
        SELECT AVG(revenus) AS moy_revenus
        FROM ventes_par_genre
    )
-- Requête principale qui utilise les deux CTEs
SELECT
    vg.genre,
    vg.revenus,
    mg.moy_revenus,
    ROUND((vg.revenus - mg.moy_revenus) / mg.moy_revenus * 100, 1) AS ecart_pct
FROM ventes_par_genre vg
    CROSS JOIN moyenne_globale mg
    -- CROSS JOIN : joint chaque ligne de vg avec chaque ligne de mg
    -- Ici mg a 1 seule ligne -> pas de produit cartésien problématique
WHERE vg.revenus > mg.moy_revenus
ORDER BY vg.revenus DESC;
```

**Avantages des CTEs** :
1. Lisibilité : chaque CTE est une étape nommée
2. Réutilisation : on peut référencer une CTE plusieurs fois
3. Débogage : on peut tester chaque CTE indépendamment
4. Performance : le moteur peut optimiser les CTEs

---

## 9. Erreurs fréquentes

### Erreur 1 : WHERE sur un alias

```sql
-- [X] ERREUR : l'alias n'existe pas encore au moment du WHERE
SELECT
    BillingCountry      AS pays,
    SUM(Total)          AS ca
FROM Invoice
WHERE ca > 100         -- ERREUR : 'ca' n'existe pas au moment du WHERE
GROUP BY BillingCountry;

-- [OK] SOLUTION : HAVING pour les conditions sur les agrégats
SELECT
    BillingCountry  AS pays,
    SUM(Total)      AS ca
FROM Invoice
GROUP BY BillingCountry
HAVING SUM(Total) > 100;  -- OK : exécuté après GROUP BY
```

### Erreur 2 : COUNT(*) vs COUNT(colonne)

```sql
-- COUNT(*) : compte TOUTES les lignes, même celles avec NULL
SELECT COUNT(*) FROM Customer;    -- -> 59 (tous les clients)

-- COUNT(colonne) : compte les valeurs NON NULL
SELECT COUNT(Company) FROM Customer;  -- -> moins de 59 (les sans entreprise)

-- COUNT(DISTINCT colonne) : compte les valeurs UNIQUES non NULL
SELECT COUNT(DISTINCT Country) FROM Customer;  -- -> 24 (24 pays)
```

### Erreur 3 : Division entière en SQL

```sql
-- [X] ERREUR : division entière en SQL (comme en Python 2 !)
SELECT 1 / 3;    -- -> 0 (pas 0.333 !)

-- [OK] SOLUTION : forcer la division décimale
SELECT 1.0 / 3;  -- -> 0.333...
SELECT CAST(1 AS REAL) / 3;  -- -> 0.333...

-- Exemple pratique avec des colonnes
SELECT
    Milliseconds / 60000        AS duree_min_entier,   -- FAUX si < 60000
    Milliseconds / 60000.0      AS duree_min_decimal    -- CORRECT
FROM Track;
```

---

## 10. Exercices

### [VERT] Exercice 1 — Requêtes de base

Écris les requêtes SQL pour :

**a)** Afficher tous les artistes dont le nom contient le mot "Band"
(insensible à la casse), avec le nombre d'albums de chacun.

**b)** Lister les 5 pistes les plus longues (en minutes), avec leur
album et leur artiste.

---

### [JAUNE] Exercice 2 — Agrégations

**a)** Pour chaque pays, calcule la facture minimale, maximale, et l'écart
entre les deux. Affiche seulement les pays où l'écart > $10.

**b)** Combien de clients ont effectué EXACTEMENT 7 achats ?
Affiche leur nom et leur total dépensé.

---

### [ROUGE] Exercice 3 — CASE WHEN et sous-requêtes

**a)** Classe chaque piste en 3 catégories selon sa durée :
- "Courte" : < 3 minutes
- "Normale" : 3 à 6 minutes
- "Longue" : > 6 minutes

Pour chaque catégorie, affiche le nombre de pistes et le prix moyen.

**b)** Affiche les pistes qui ont généré plus de revenus que
la **médiane** des revenus par piste.
(Indice : utilise une CTE + NTILE ou ORDER BY + LIMIT/OFFSET)

---

## 11. Corrigés

### Corrigé Exercice 1a — Artistes avec "Band"

```sql
SELECT
    ar.Name                 AS artiste,
    COUNT(al.AlbumId)       AS nb_albums
FROM Artist ar
    LEFT JOIN Album al ON ar.ArtistId = al.ArtistId
WHERE LOWER(ar.Name) LIKE '%band%'
    -- LOWER() : convertit en minuscules avant la comparaison
    -- LIKE '%band%' : contient 'band' n'importe où
ORDER BY nb_albums DESC;

-- Explication :
-- LOWER(ar.Name) -> 'the beatles' -> comparé à '%band%'
-- LIKE est déjà insensible à la casse en SQLite pour ASCII
-- mais LOWER() garantit le comportement correct pour les accents
```

### Corrigé Exercice 1b — 5 pistes les plus longues

```sql
SELECT
    t.Name                                      AS piste,
    al.Title                                    AS album,
    ar.Name                                     AS artiste,
    ROUND(t.Milliseconds / 60000.0, 2)          AS duree_min,
    -- t.Milliseconds / 60000.0 :
    -- 60000 = 60 secondes × 1000 millisecondes = 1 minute
    -- .0 force la division décimale
    t.UnitPrice                                 AS prix_usd
FROM Track t
    JOIN Album al   ON t.AlbumId    = al.AlbumId
    JOIN Artist ar  ON al.ArtistId  = ar.ArtistId
ORDER BY t.Milliseconds DESC
    -- Tri par durée (Milliseconds) DESC = plus longue en premier
LIMIT 5;

-- Résultat attendu (extrait) :
-- piste                        album          artiste       duree_min
-- ----------------------------- -------------- ------------- ---------
-- Occupation / Precipice        Lost, Season 3 Various       88.37
-- Through a Looking Glass       Lost, Season 3 Various       84.65
-- ...
```

### Corrigé Exercice 2a — Pays avec écart de panier > $10

```sql
SELECT
    BillingCountry                              AS pays,
    ROUND(MIN(Total), 2)                        AS facture_min,
    ROUND(MAX(Total), 2)                        AS facture_max,
    ROUND(MAX(Total) - MIN(Total), 2)           AS ecart,
    COUNT(InvoiceId)                            AS nb_factures
FROM Invoice
GROUP BY BillingCountry
HAVING (MAX(Total) - MIN(Total)) > 10
    -- HAVING sur le calcul (pas l'alias 'ecart')
    -- L'alias n'est pas encore défini au moment du HAVING dans certains SGBD
ORDER BY ecart DESC;
```

### Corrigé Exercice 2b — Clients avec exactement 7 achats

```sql
SELECT
    c.FirstName || ' ' || c.LastName    AS client,
    c.Country,
    COUNT(i.InvoiceId)                  AS nb_achats,
    ROUND(SUM(i.Total), 2)              AS total_depense
FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
HAVING COUNT(i.InvoiceId) = 7
    -- HAVING COUNT = 7 : exactement 7 factures
ORDER BY total_depense DESC;
```

### Corrigé Exercice 3a — Catégories de durée de pistes

```sql
SELECT
    CASE
        WHEN t.Milliseconds < 3 * 60 * 1000  THEN 'Courte (< 3 min)'
        -- 3 * 60 * 1000 = 180 000 ms = 3 minutes
        WHEN t.Milliseconds <= 6 * 60 * 1000 THEN 'Normale (3-6 min)'
        -- 6 * 60 * 1000 = 360 000 ms = 6 minutes
        ELSE                                       'Longue (> 6 min)'
    END                                         AS categorie_duree,

    COUNT(t.TrackId)                            AS nb_pistes,
    ROUND(AVG(t.UnitPrice), 2)                  AS prix_moyen,
    ROUND(AVG(t.Milliseconds) / 60000.0, 2)     AS duree_moy_min

FROM Track t
GROUP BY categorie_duree
    -- On peut grouper par un CASE WHEN (calculé dans le SELECT)
    -- SQLite l'évalue correctement
ORDER BY duree_moy_min;
```

### Corrigé Exercice 3b — Pistes au-dessus de la médiane

```sql
-- Approche : calculer la médiane via LIMIT/OFFSET
WITH
    revenus_par_piste AS (
        -- CTE 1 : revenus par piste
        SELECT
            t.TrackId,
            t.Name                          AS piste,
            ar.Name                         AS artiste,
            COALESCE(SUM(il.UnitPrice), 0)  AS revenus
        FROM Track t
            LEFT JOIN InvoiceLine il ON t.TrackId   = il.TrackId
            LEFT JOIN Album al       ON t.AlbumId   = al.AlbumId
            LEFT JOIN Artist ar      ON al.ArtistId = ar.ArtistId
        GROUP BY t.TrackId, t.Name, ar.Name
    ),
    mediane AS (
        -- CTE 2 : calcul de la médiane via tri + LIMIT/OFFSET
        -- La médiane est la valeur du milieu quand les données sont triées
        SELECT AVG(revenus) AS valeur_mediane
        FROM (
            SELECT revenus
            FROM revenus_par_piste
            ORDER BY revenus
            -- LIMIT 2 : prend 2 valeurs autour du milieu
            -- OFFSET (COUNT/2 - 1) : saute jusqu'au milieu
            -- Approximation : on prend la moyenne des 2 valeurs centrales
            LIMIT 2 OFFSET (
                SELECT COUNT(*) / 2 - 1
                FROM revenus_par_piste
                WHERE revenus > 0
            )
        )
    )
SELECT
    rp.piste,
    rp.artiste,
    ROUND(rp.revenus, 2)    AS revenus_usd,
    ROUND(m.valeur_mediane, 2) AS mediane_usd
FROM revenus_par_piste rp
    CROSS JOIN mediane m
WHERE rp.revenus > m.valeur_mediane
ORDER BY rp.revenus DESC
LIMIT 20;
```

---

## 12. Récapitulatif

### Ce que tu as appris dans la Partie 2

| Compétence SQL | Maîtrisée |
|---------------|-----------|
| Ordre d'écriture vs ordre d'exécution SQL | [OK] |
| WHERE avec tous les opérateurs | [OK] |
| GROUP BY + HAVING (différence avec WHERE) | [OK] |
| COUNT(*) vs COUNT(col) vs COUNT(DISTINCT) | [OK] |
| Fonctions de texte (UPPER, LOWER, SUBSTR, TRIM, LENGTH) | [OK] |
| Fonctions de date (strftime) | [OK] |
| Fonctions numériques (ROUND, ABS, CAST) | [OK] |
| CASE WHEN (classement, catégorisation) | [OK] |
| Sous-requêtes scalaires | [OK] |
| CTEs (WITH ... AS) | [OK] |
| Division décimale (1.0 / n) | [OK] |
| Paramètres sécurisés (:param ou ?) | [OK] |

### Prochaine étape

**Partie 3 : Jointures Avancées et Analyses Multi-Tables**
- INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
- Auto-jointures (Employee hiérarchie)
- Jointures multiples (3+ tables)
- CROSS JOIN et ses usages
- Sous-requêtes EXISTS et IN
- Window Functions (ROW_NUMBER, RANK, LAG, LEAD)

---

*DataInsight Pro SQL Edition — Partie 2/8 | Base : Chinook Music Database*

# DataInsight Pro — SQL Edition
# Parties 3 à 8 : Jointures, Nettoyage, EDA, Visualisation, Cas Business, Projet Final

---

# PARTIE 3 : Jointures Avancées et Analyses Multi-Tables

---

## Objectifs pédagogiques

| Concept | Couverture |
|---------|-----------|
| INNER JOIN, LEFT/RIGHT JOIN, FULL OUTER JOIN | [OK] |
| Auto-jointures (Employee hiérarchie) | [OK] |
| Jointures multiples (4+ tables) | [OK] |
| EXISTS et NOT EXISTS | [OK] |
| IN vs EXISTS (performance) | [OK] |
| Window Functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) | [OK] |
| UNION et UNION ALL | [OK] |

---

## Théorie : les types de jointures

```sql
-- Données d'exemple pour illustrer :
-- Table A : clients       Table B : factures
-- id | nom               id | client_id | montant
-- ---|----               ---|-----------|--------
-- 1  | Alice             1  | 1         | 100
-- 2  | Bob               2  | 1         | 200
-- 3  | Charlie           3  | 3         | 150
--    (Bob n'a pas de facture)

-- INNER JOIN : seulement les lignes qui ont une correspondance des deux côtés
SELECT a.nom, b.montant
FROM A a INNER JOIN B b ON a.id = b.client_id;
-- Résultat : Alice/100, Alice/200, Charlie/150 (Bob absent)

-- LEFT JOIN : toutes les lignes de GAUCHE + correspondances de droite
SELECT a.nom, b.montant
FROM A a LEFT JOIN B b ON a.id = b.client_id;
-- Résultat : Alice/100, Alice/200, Bob/NULL, Charlie/150

-- RIGHT JOIN : toutes les lignes de DROITE + correspondances de gauche
-- (équivalent à inverser LEFT JOIN — rare dans la pratique)

-- FULL OUTER JOIN : toutes les lignes des deux côtés
-- (NON supporté en SQLite — utiliser LEFT JOIN UNION RIGHT JOIN)
```

---

## Code : `src/queries_advanced.py`

```python
"""
queries_advanced.py — Requêtes SQL avancées avec jointures et window functions
"""

# ============================================================
# JOINTURES MULTI-TABLES (4+ tables)
# ============================================================

DETAIL_VENTES_COMPLET = """
    SELECT
        i.InvoiceId,
        i.InvoiceDate,
        c.FirstName || ' ' || c.LastName    AS client,
        c.Country                           AS pays_client,
        t.Name                              AS piste,
        ar.Name                             AS artiste,
        al.Title                            AS album,
        g.Name                              AS genre,
        mt.Name                             AS format,
        il.UnitPrice                        AS prix_paye,
        il.Quantity                         AS quantite
    FROM Invoice i
        JOIN Customer c     ON i.CustomerId     = c.CustomerId
        JOIN InvoiceLine il ON i.InvoiceId      = il.InvoiceId
        JOIN Track t        ON il.TrackId       = t.TrackId
        JOIN Album al       ON t.AlbumId        = al.AlbumId
        JOIN Artist ar      ON al.ArtistId      = ar.ArtistId
        JOIN Genre g        ON t.GenreId        = g.GenreId
        JOIN MediaType mt   ON t.MediaTypeId    = mt.MediaTypeId
    ORDER BY i.InvoiceDate DESC
"""
# Cette requête joint 8 tables en une seule requête
# L'ordre des JOINs suit le flux logique : facture -> client -> lignes -> pistes -> ...

# ============================================================
# WINDOW FUNCTIONS
# ============================================================

RANG_CLIENTS_PAR_PAYS = """
    WITH ltv AS (
        SELECT
            c.CustomerId,
            c.FirstName || ' ' || c.LastName    AS client,
            c.Country,
            SUM(i.Total)                        AS total_achats
        FROM Customer c
            JOIN Invoice i ON c.CustomerId = i.CustomerId
        GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    )
    SELECT
        client,
        Country                         AS pays,
        ROUND(total_achats, 2)          AS total_achats,

        -- ROW_NUMBER : numéro de ligne unique (1, 2, 3...)
        ROW_NUMBER() OVER (
            PARTITION BY Country        -- Redémarre à 1 pour chaque pays
            ORDER BY total_achats DESC  -- Classé par total décroissant
        )                               AS rang_dans_pays,

        -- RANK : même rang si ex-aequo, puis saute des rangs (1, 1, 3...)
        RANK() OVER (
            ORDER BY total_achats DESC  -- Rang global (tous pays confondus)
        )                               AS rang_global,

        -- DENSE_RANK : même rang si ex-aequo, sans saut (1, 1, 2...)
        DENSE_RANK() OVER (
            ORDER BY total_achats DESC
        )                               AS rang_dense,

        -- SUM OVER : total cumulé ou total global
        ROUND(
            SUM(total_achats) OVER(),   -- Total global (toutes lignes)
            2
        )                               AS ca_total_global,

        -- Part de marché de ce client
        ROUND(
            total_achats * 100.0 / SUM(total_achats) OVER(),
            2
        )                               AS part_marche_pct

    FROM ltv
    ORDER BY total_achats DESC
"""

EVOLUTION_CA_AVEC_PRECEDENT = """
    WITH ca_mensuel AS (
        SELECT
            strftime('%Y-%m', InvoiceDate)  AS mois,
            ROUND(SUM(Total), 2)            AS ca
        FROM Invoice
        GROUP BY mois
    )
    SELECT
        mois,
        ca                              AS ca_mensuel,

        -- LAG : valeur du mois précédent
        LAG(ca, 1) OVER (ORDER BY mois) AS ca_mois_precedent,
        -- LAG(valeur, n_lignes_en_arrière) OVER (ORDER BY ...)
        -- LAG(ca, 1) = valeur de la ligne précédente
        -- LAG(ca, 3) = valeur d'il y a 3 lignes

        -- Croissance mensuelle (%)
        ROUND(
            (ca - LAG(ca, 1) OVER (ORDER BY mois))
            / LAG(ca, 1) OVER (ORDER BY mois)
            * 100,
            1
        )                               AS croissance_pct,

        -- LEAD : valeur du mois suivant (utile pour les prévisions)
        LEAD(ca, 1) OVER (ORDER BY mois) AS ca_mois_suivant,

        -- Cumul du CA depuis le début
        ROUND(
            SUM(ca) OVER (ORDER BY mois ROWS UNBOUNDED PRECEDING),
            2
        )                               AS ca_cumule
        -- ROWS UNBOUNDED PRECEDING : toutes les lignes depuis le début jusqu'à la ligne courante

    FROM ca_mensuel
    ORDER BY mois
"""

TOP_PISTE_PAR_GENRE = """
    WITH revenus_pistes AS (
        SELECT
            t.TrackId,
            t.Name                              AS piste,
            g.Name                              AS genre,
            ar.Name                             AS artiste,
            COALESCE(SUM(il.UnitPrice), 0)      AS revenus
        FROM Track t
            JOIN Genre g        ON t.GenreId    = g.GenreId
            JOIN Album al       ON t.AlbumId    = al.AlbumId
            JOIN Artist ar      ON al.ArtistId  = ar.ArtistId
            LEFT JOIN InvoiceLine il ON t.TrackId = il.TrackId
        GROUP BY t.TrackId, t.Name, g.Name, ar.Name
    )
    SELECT *
    FROM (
        SELECT
            piste,
            genre,
            artiste,
            ROUND(revenus, 2)               AS revenus_usd,
            ROW_NUMBER() OVER (
                PARTITION BY genre
                ORDER BY revenus DESC
            )                               AS rang_dans_genre
        FROM revenus_pistes
    )
    WHERE rang_dans_genre = 1
        -- Ne garde que le n°1 de chaque genre (rang = 1)
    ORDER BY revenus_usd DESC
"""

# ============================================================
# EXISTS / NOT EXISTS
# ============================================================

CLIENTS_AVEC_ACHAT_ROCK = """
    SELECT DISTINCT
        c.FirstName || ' ' || c.LastName    AS client,
        c.Country
    FROM Customer c
    WHERE EXISTS (
        -- EXISTS : vrai si la sous-requête retourne AU MOINS une ligne
        SELECT 1
        FROM Invoice i
            JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
            JOIN Track t        ON il.TrackId  = t.TrackId
            JOIN Genre g        ON t.GenreId   = g.GenreId
        WHERE i.CustomerId = c.CustomerId   -- Corrélation : référence la ligne externe
          AND g.Name = 'Rock'
    )
    ORDER BY c.Country, client
"""
# EXISTS est plus efficace que IN pour les grandes tables car :
# - EXISTS s'arrête dès qu'il trouve UNE correspondance
# - IN doit d'abord construire la liste complète des valeurs

CLIENTS_SANS_ACHAT_ROCK = """
    SELECT
        c.FirstName || ' ' || c.LastName    AS client,
        c.Country
    FROM Customer c
    WHERE NOT EXISTS (
        -- NOT EXISTS : vrai si la sous-requête ne retourne AUCUNE ligne
        SELECT 1
        FROM Invoice i
            JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
            JOIN Track t        ON il.TrackId  = t.TrackId
            JOIN Genre g        ON t.GenreId   = g.GenreId
        WHERE i.CustomerId = c.CustomerId
          AND g.Name = 'Rock'
    )
    ORDER BY c.Country, client
"""

# ============================================================
# UNION ALL
# ============================================================

RAPPORT_ACTIVITE_COMPLET = """
    -- Combiner plusieurs analyses en un seul rapport
    SELECT 'Pays'       AS dimension, BillingCountry  AS valeur, ROUND(SUM(Total), 2) AS ca
    FROM Invoice GROUP BY BillingCountry

    UNION ALL

    SELECT 'Genre', g.Name, ROUND(SUM(il.UnitPrice), 2)
    FROM Genre g
        JOIN Track t        ON g.GenreId  = t.GenreId
        JOIN InvoiceLine il ON t.TrackId  = il.TrackId
    GROUP BY g.Name

    UNION ALL

    SELECT 'Année', strftime('%Y', InvoiceDate), ROUND(SUM(Total), 2)
    FROM Invoice
    GROUP BY strftime('%Y', InvoiceDate)

    ORDER BY dimension, ca DESC
"""
```

---

## Exercices Partie 3

### [JAUNE] Exercice — Window Function : Percentiles

Classe chaque client dans un quintile (1 à 5) selon son LTV total,
avec NTILE(5). Affiche le client, son LTV, son quintile, et
le LTV moyen de son quintile.

### Corrigé

```sql
WITH ltv_clients AS (
    SELECT
        c.FirstName || ' ' || c.LastName    AS client,
        SUM(i.Total)                        AS ltv
    FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName
),
avec_quintile AS (
    SELECT
        client,
        ROUND(ltv, 2)           AS ltv_usd,
        NTILE(5) OVER (
            ORDER BY ltv ASC    -- 1 = les moins dépensiers, 5 = les plus
        )                       AS quintile
    FROM ltv_clients
)
SELECT
    client,
    ltv_usd,
    quintile,
    ROUND(AVG(ltv_usd) OVER (PARTITION BY quintile), 2) AS ltv_moy_quintile
FROM avec_quintile
ORDER BY quintile DESC, ltv_usd DESC;
```

---

---

# PARTIE 4 : Nettoyage des Données avec Python + SQL

---

## Objectifs pédagogiques

| Concept | Couverture |
|---------|-----------|
| Détection des valeurs manquantes via SQL | [OK] |
| Imputation avec COALESCE | [OK] |
| Nettoyage Python post-chargement | [OK] |
| Détection des doublons SQL | [OK] |
| Normalisation des types | [OK] |
| Audit de qualité complet | [OK] |

---

## Code : `src/data_cleaning.py`

```python
"""
data_cleaning.py — Nettoyage des données chargées depuis Chinook

Règle : on ne modifie PAS la base SQL (données de référence intouchables).
On nettoie en mémoire dans les DataFrames pandas.
"""

import pandas as pd
import numpy as np
import logging
from pathlib import Path
import sys
sys.path.insert(0, str(Path(__file__).parent.parent))

from src.db_connection import DatabaseConnection
from src.utils import timeit, rapport_qualite_sql

logger = logging.getLogger(__name__)


class ChinookCleaner:
    """
    Nettoyeur de données pour la base Chinook.

    Applique toutes les transformations nécessaires sur les DataFrames
    chargés depuis SQL, sans modifier la base de données.

    Example:
        >>> cleaner = ChinookCleaner()
        >>> df_clients = cleaner.nettoyer_clients()
        >>> df_factures = cleaner.nettoyer_factures()
    """

    def __init__(self):
        self.journal = []
        # journal : liste des transformations appliquées (traçabilité)

    def _log_transformation(self, etape: str, avant: int, apres: int) -> None:
        """Enregistre une transformation dans le journal."""
        supprimees = avant - apres
        self.journal.append({
            "etape": etape,
            "avant": avant,
            "apres": apres,
            "supprimees": supprimees,
            "pct_impact": round(supprimees / max(avant, 1) * 100, 2)
        })
        logger.info(f"{etape} : {avant} -> {apres} ({supprimees} lignes impactées)")

    # ----------------------------------------------------------
    # AUDIT SQL : détecter les problèmes DANS la base
    # ----------------------------------------------------------

    def audit_qualite_sql(self) -> dict:
        """
        Exécute des requêtes SQL pour détecter les problèmes de qualité.

        Vérifie directement dans SQLite :
        - Valeurs NULL par table et colonne
        - Doublons potentiels
        - Contraintes métier violées

        Returns:
            dict : Rapport de qualité avec sous-dictionnaires par problème
        """
        rapport = {}

        with DatabaseConnection() as conn:

            # ---- NaN dans Customer ----
            query_nan_customers = """
                SELECT
                    COUNT(*)                            AS total,
                    SUM(CASE WHEN Company     IS NULL THEN 1 ELSE 0 END) AS null_company,
                    SUM(CASE WHEN State       IS NULL THEN 1 ELSE 0 END) AS null_state,
                    SUM(CASE WHEN PostalCode  IS NULL THEN 1 ELSE 0 END) AS null_postalcode,
                    SUM(CASE WHEN Phone       IS NULL THEN 1 ELSE 0 END) AS null_phone,
                    SUM(CASE WHEN Fax         IS NULL THEN 1 ELSE 0 END) AS null_fax,
                    SUM(CASE WHEN SupportRepId IS NULL THEN 1 ELSE 0 END) AS null_supportrep
                FROM Customer
            """
            rapport["null_customers"] = pd.read_sql(query_nan_customers, conn)

            # ---- Artistes sans albums ----
            query_artistes_sans_album = """
                SELECT ar.Name AS artiste
                FROM Artist ar
                    LEFT JOIN Album al ON ar.ArtistId = al.ArtistId
                WHERE al.AlbumId IS NULL
                ORDER BY ar.Name
            """
            rapport["artistes_sans_album"] = pd.read_sql(query_artistes_sans_album, conn)

            # ---- Pistes sans compositeur ----
            query_tracks_no_composer = """
                SELECT COUNT(*) AS nb_sans_compositeur,
                       ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Track), 1) AS pct
                FROM Track
                WHERE Composer IS NULL
            """
            rapport["tracks_no_composer"] = pd.read_sql(query_tracks_no_composer, conn)

            # ---- Prix incohérents (Track vs InvoiceLine) ----
            query_prix_incoherent = """
                SELECT
                    t.Name          AS piste,
                    t.UnitPrice     AS prix_catalogue,
                    il.UnitPrice    AS prix_facture,
                    il.UnitPrice - t.UnitPrice AS difference
                FROM Track t
                    JOIN InvoiceLine il ON t.TrackId = il.TrackId
                WHERE ABS(il.UnitPrice - t.UnitPrice) > 0.01
                    -- Différence de plus de 1 centime -> suspect
                LIMIT 20
            """
            rapport["prix_incoherents"] = pd.read_sql(query_prix_incoherent, conn)

        return rapport

    # ----------------------------------------------------------
    # NETTOYAGE PYTHON des DataFrames
    # ----------------------------------------------------------

    @timeit
    def nettoyer_clients(self) -> pd.DataFrame:
        """
        Charge et nettoie les données clients.

        Transformations appliquées :
        1. Remplacement des NaN par des valeurs par défaut
        2. Normalisation des chaînes (strip, title case)
        3. Création d'une colonne nom_complet
        4. Conversion du type de SupportRepId

        Returns:
            pd.DataFrame : Clients nettoyés
        """
        with DatabaseConnection() as conn:
            df = pd.read_sql("SELECT * FROM Customer", conn)

        n_initial = len(df)

        # ---- 1. Valeurs manquantes ----
        # Company : remplacer NULL par "Particulier"
        df["Company"] = df["Company"].fillna("Particulier")
        # fillna() : remplace les NaN par la valeur donnée

        # State : problématique (beaucoup de pays n'ont pas d'état)
        # On remplace par le pays pour avoir un niveau géographique
        df["State"] = df["State"].fillna(df["Country"])
        # fillna(autre_colonne) : remplace chaque NaN par la valeur de l'autre colonne
        # Cette technique est appelée "imputation par une autre colonne"

        # Phone / Fax : remplacer par "N/A" (pas de valeur numérique à imputer)
        for col in ["Phone", "Fax"]:
            df[col] = df[col].fillna("N/A")

        # PostalCode : remplacer par "0000" (code postal générique)
        df["PostalCode"] = df["PostalCode"].fillna("0000")

        # ---- 2. Normalisation des chaînes ----
        for col in ["FirstName", "LastName", "City", "Country"]:
            df[col] = (df[col]
                       .str.strip()     # Supprimer les espaces en début/fin
                       .str.title())    # "alice MARTIN" -> "Alice Martin"

        # ---- 3. Colonne calculée ----
        df["nom_complet"] = df["FirstName"] + " " + df["LastName"]

        # ---- 4. Types ----
        # SupportRepId peut être NULL (client sans agent) -> utiliser Int64 nullable
        df["SupportRepId"] = df["SupportRepId"].astype("Int64")
        # Int64 (majuscule) : entier nullable en pandas
        # int64 (minuscule) : entier non nullable (erreur si NaN)

        self._log_transformation("nettoyer_clients", n_initial, len(df))
        logger.info(f"Clients nettoyés : {len(df)} lignes")
        return df

    @timeit
    def nettoyer_factures(self) -> pd.DataFrame:
        """
        Charge et nettoie les factures avec leurs lignes.

        Returns:
            pd.DataFrame : Factures enrichies avec colonnes calculées
        """
        with DatabaseConnection() as conn:
            # Jointure Invoice + InvoiceLine + Track pour avoir le détail
            query = """
                SELECT
                    i.InvoiceId,
                    i.CustomerId,
                    i.InvoiceDate,
                    i.BillingCountry,
                    i.Total                 AS total_facture,
                    il.InvoiceLineId,
                    il.TrackId,
                    il.UnitPrice            AS prix_paye,
                    il.Quantity,
                    t.Name                  AS nom_piste,
                    t.Milliseconds
                FROM Invoice i
                    JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
                    JOIN Track t       ON il.TrackId   = t.TrackId
            """
            df = pd.read_sql(query, conn)

        n_initial = len(df)

        # ---- Conversion des dates ----
        df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
        # pd.to_datetime() : convertit string -> datetime64

        # ---- Extraction des composantes temporelles ----
        df["annee"]     = df["InvoiceDate"].dt.year
        df["mois"]      = df["InvoiceDate"].dt.month
        df["trimestre"] = df["InvoiceDate"].dt.quarter
        df["jour_semaine"] = df["InvoiceDate"].dt.day_name()
        # .dt. : accesseur datetime de pandas
        # .dt.year : année (entier)
        # .dt.day_name() : "Monday", "Tuesday", etc.

        # ---- Durée en minutes ----
        df["duree_min"] = (df["Milliseconds"] / 60000.0).round(2)

        # ---- Montant total par ligne ----
        df["montant_ligne"] = (df["prix_paye"] * df["Quantity"]).round(2)

        self._log_transformation("nettoyer_factures", n_initial, len(df))
        return df

    def rapport_nettoyage(self) -> pd.DataFrame:
        """Retourne le journal de toutes les transformations."""
        return pd.DataFrame(self.journal)
```

---

## Requêtes SQL de nettoyage (à inclure dans `queries.py`)

```sql
-- Détecter les doublons sur une combinaison de colonnes
-- (ici : même client dans le même pays avec même email)
SELECT
    FirstName, LastName, Country, Email,
    COUNT(*) AS nb_occurrences
FROM Customer
GROUP BY FirstName, LastName, Country, Email
HAVING COUNT(*) > 1;

-- Cohérence des montants : vérifier que Invoice.Total = SUM(InvoiceLine)
SELECT
    i.InvoiceId,
    i.Total                             AS total_facture,
    ROUND(SUM(il.UnitPrice * il.Quantity), 2) AS total_calcule,
    ABS(i.Total - SUM(il.UnitPrice * il.Quantity)) AS ecart
FROM Invoice i
    JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
GROUP BY i.InvoiceId
HAVING ABS(i.Total - SUM(il.UnitPrice * il.Quantity)) > 0.01;
-- Si cette requête retourne des lignes -> incohérence dans la base
```

---

---

# PARTIE 5 : EDA (Exploratory Data Analysis)

---

## Code : `src/analysis.py`

```python
"""
analysis.py — Analyse exploratoire des données Chinook

Ce module calcule les KPIs et statistiques descriptives
pour répondre aux questions business.
"""

import pandas as pd
import numpy as np
import logging
from pathlib import Path
import sys
sys.path.insert(0, str(Path(__file__).parent.parent))

from src.db_connection import DatabaseConnection
from src.data_loader import (
    charger_ltv_clients, charger_ca_pays,
    charger_ca_mensuel, charger_performance_agents, charger_genres
)
from src.utils import timeit

logger = logging.getLogger(__name__)


class ChinookAnalyzer:
    """
    Analyse exploratoire de la base Chinook.

    Example:
        >>> analyzer = ChinookAnalyzer()
        >>> kpis = analyzer.kpis_globaux()
        >>> print(kpis)
    """

    @timeit
    def kpis_globaux(self) -> dict:
        """
        Calcule les KPIs principaux de Chinook Music Store.

        Returns:
            dict : KPIs avec clés ca_total, nb_factures, panier_moyen, etc.
        """
        query = """
            SELECT
                COUNT(DISTINCT c.CustomerId)        AS nb_clients,
                COUNT(DISTINCT i.InvoiceId)         AS nb_factures,
                ROUND(SUM(i.Total), 2)              AS ca_total,
                ROUND(AVG(i.Total), 2)              AS panier_moyen,
                ROUND(MIN(i.Total), 2)              AS facture_min,
                ROUND(MAX(i.Total), 2)              AS facture_max,
                COUNT(DISTINCT t.TrackId)           AS nb_pistes_vendues,
                COUNT(DISTINCT ar.ArtistId)         AS nb_artistes_vendus,
                COUNT(DISTINCT i.BillingCountry)    AS nb_pays
            FROM Invoice i
                JOIN Customer c     ON i.CustomerId = c.CustomerId
                JOIN InvoiceLine il ON i.InvoiceId  = il.InvoiceId
                JOIN Track t        ON il.TrackId   = t.TrackId
                JOIN Album al       ON t.AlbumId    = al.AlbumId
                JOIN Artist ar      ON al.ArtistId  = ar.ArtistId
        """
        with DatabaseConnection() as conn:
            df = pd.read_sql(query, conn)

        # Conversion en dictionnaire simple
        kpis = df.iloc[0].to_dict()
        # df.iloc[0] : première (et seule) ligne
        # .to_dict() : convertit la ligne en dictionnaire

        logger.info(f"KPIs calculés : CA={kpis['ca_total']:,} USD")
        return kpis

    @timeit
    def analyse_geographique(self) -> pd.DataFrame:
        """
        Analyse complète des ventes par pays.

        Returns:
            pd.DataFrame : Métriques par pays avec part de marché
        """
        df = charger_ca_pays()

        # Calcul de la part de marché
        total_ca = df["ca_total"].sum()
        df["part_marche_pct"] = (df["ca_total"] / total_ca * 100).round(2)

        # Cumul pour courbe de Pareto
        df = df.sort_values("ca_total", ascending=False).reset_index(drop=True)
        df["ca_cumule"] = df["ca_total"].cumsum()
        df["part_cumul_pct"] = (df["ca_cumule"] / total_ca * 100).round(1)
        # cumsum() : somme cumulée (running total)

        return df

    @timeit
    def analyse_temporelle(self) -> pd.DataFrame:
        """
        Analyse de l'évolution temporelle du CA.

        Returns:
            pd.DataFrame : CA mensuel avec croissance et tendance
        """
        df = charger_ca_mensuel()

        # Croissance mensuelle
        df["croissance_pct"] = df["ca_mensuel"].pct_change() * 100
        # pct_change() : (val_courante - val_precedente) / val_precedente × 100

        # Moyenne mobile 3 mois (lisse les variations)
        df["ma3_mensuel"] = df["ca_mensuel"].rolling(window=3, min_periods=1).mean()
        # rolling(window=3) : fenêtre glissante de 3 mois
        # min_periods=1 : calcule même avec moins de 3 valeurs au début

        return df

    @timeit
    def analyse_clients(self) -> pd.DataFrame:
        """
        Analyse complète des clients avec segmentation.

        Returns:
            pd.DataFrame : Clients avec segment et métriques
        """
        df = charger_ltv_clients()

        # Segmentation par LTV (quartiles)
        df["segment_ltv"] = pd.qcut(
            df["ltv_usd"],
            q=4,
            labels=["Bronze", "Silver", "Gold", "Platinum"]
        )
        # pd.qcut : divise en quantiles égaux
        # q=4 : 4 quartiles (25% chacun)

        # Durée de relation en jours
        df["duree_relation_jours"] = (
            pd.to_datetime(df["dernier_achat"])
            - pd.to_datetime(df["premier_achat"])
        ).dt.days

        return df

    @timeit
    def analyse_genres(self) -> pd.DataFrame:
        """
        Analyse des genres avec métriques de performance.
        """
        df = charger_genres()

        # Part de marché par genre
        total = df["revenus_usd"].sum()
        df["part_marche"] = (df["revenus_usd"] / total * 100).round(1)

        return df

    @timeit
    def statistiques_descriptives(self, table: str = "Invoice") -> pd.DataFrame:
        """
        Calcule les statistiques descriptives d'une table.

        Args:
            table : Nom de la table SQL

        Returns:
            pd.DataFrame : Statistiques (count, mean, std, min, quartiles, max)
        """
        with DatabaseConnection() as conn:
            df = pd.read_sql(f"SELECT * FROM {table}", conn)

        # Sélection des colonnes numériques uniquement
        df_num = df.select_dtypes(include=[np.number])
        # select_dtypes(include=[np.number]) : colonnes int et float

        # describe() : statistiques en une ligne
        stats = df_num.describe().round(2)
        # Retourne : count, mean, std, min, 25%, 50%, 75%, max

        return stats
```

---

## Requêtes SQL pour l'EDA

```sql
-- Distribution des prix de pistes
SELECT
    UnitPrice,
    COUNT(*) AS nb_pistes,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Track), 1) AS pct
FROM Track
GROUP BY UnitPrice
ORDER BY UnitPrice;

-- Résultat : 99.5% à $0.99, 0.5% à $1.99

-- Statistiques sur les factures
SELECT
    ROUND(AVG(Total), 2)    AS moyenne,
    ROUND(MIN(Total), 2)    AS minimum,
    ROUND(MAX(Total), 2)    AS maximum,
    -- Médiane : pas de fonction native SQLite
    (
        SELECT ROUND(AVG(Total), 2) FROM (
            SELECT Total FROM Invoice ORDER BY Total
            LIMIT 2 OFFSET (SELECT COUNT(*) FROM Invoice) / 2 - 1
        )
    )                       AS mediane_approx
FROM Invoice;

-- Détection des outliers (méthode IQR en SQL)
WITH quartiles AS (
    SELECT
        Total,
        NTILE(4) OVER (ORDER BY Total) AS quartile
    FROM Invoice
),
iqr AS (
    SELECT
        MAX(CASE WHEN quartile = 1 THEN Total END) AS q1,
        MAX(CASE WHEN quartile = 3 THEN Total END) AS q3,
        MAX(CASE WHEN quartile = 3 THEN Total END)
        - MAX(CASE WHEN quartile = 1 THEN Total END) AS iqr_value
    FROM quartiles
)
SELECT i.InvoiceId, i.Total, 'Outlier' AS statut
FROM Invoice i, iqr
WHERE i.Total < iqr.q1 - 1.5 * iqr.iqr_value
   OR i.Total > iqr.q3 + 1.5 * iqr.iqr_value;
```

---

---

# PARTIE 6 : Visualisation Professionnelle

---

## Code : `src/visualization.py`

```python
"""
visualization.py — Graphiques professionnels pour les analyses Chinook

Tous les graphiques sont sauvegardés dans reports/figures/
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.gridspec as gridspec
import seaborn as sns
from pathlib import Path
import logging
import sys
sys.path.insert(0, str(Path(__file__).parent.parent))

from src.utils import FIGURES_DIR
from src.analysis import ChinookAnalyzer

logger = logging.getLogger(__name__)

# Style global
plt.style.use("seaborn-v0_8-whitegrid")
sns.set_palette("husl")


class ChinookVisualizer:
    """
    Générateur de visualisations pour Chinook Music Store.

    Example:
        >>> viz = ChinookVisualizer()
        >>> viz.dashboard_kpis()
        >>> viz.evolution_ca()
    """

    def __init__(self):
        self.analyzer = ChinookAnalyzer()
        FIGURES_DIR.mkdir(parents=True, exist_ok=True)

    def _sauvegarder(self, nom: str) -> Path:
        """Sauvegarde la figure courante."""
        chemin = FIGURES_DIR / f"{nom}.png"
        plt.savefig(chemin, dpi=150, bbox_inches="tight")
        plt.close()
        logger.info(f"Graphique sauvegardé : {chemin}")
        return chemin

    def dashboard_kpis(self) -> Path:
        """
        Dashboard avec les 6 KPIs principaux en cartes colorées.
        """
        kpis = self.analyzer.kpis_globaux()

        fig, axes = plt.subplots(2, 3, figsize=(14, 7))
        fig.suptitle("DataInsight Pro — Chinook Music Store\nKPIs Globaux",
                     fontsize=14, fontweight="bold")

        cartes = [
            ("[ARGENT] CA Total",        f"${kpis['ca_total']:,.2f}",      "#3498db"),
            ("[FICHIER] Factures",         f"{kpis['nb_factures']:,}",        "#2ecc71"),
            ("[SHOPPING_TROLLEY] Panier Moyen",     f"${kpis['panier_moyen']:.2f}",   "#e74c3c"),
            ("[UTILISATEUR] Clients",          f"{kpis['nb_clients']}",           "#9b59b6"),
            ("[SON] Pistes Vendues",   f"{kpis['nb_pistes_vendues']:,}", "#f39c12"),
            ("[MONDE] Pays",             f"{kpis['nb_pays']}",              "#1abc9c"),
        ]

        for ax, (label, valeur, couleur) in zip(axes.flat, cartes):
            ax.set_facecolor(couleur)
            ax.text(0.5, 0.6, valeur, ha="center", va="center",
                    fontsize=20, fontweight="bold", color="white",
                    transform=ax.transAxes)
            ax.text(0.5, 0.25, label, ha="center", va="center",
                    fontsize=11, color="white", transform=ax.transAxes)
            ax.axis("off")

        plt.tight_layout()
        return self._sauvegarder("dashboard_kpis")

    def evolution_ca(self) -> Path:
        """
        Graphique d'évolution du CA mensuel avec moyenne mobile.
        """
        df = self.analyzer.analyse_temporelle()

        fig, axes = plt.subplots(2, 1, figsize=(14, 8), sharex=True)
        fig.suptitle("Évolution du Chiffre d'Affaires Mensuel", fontweight="bold")

        # Axe 1 : CA mensuel + moyenne mobile
        ax1 = axes[0]
        ax1.bar(range(len(df)), df["ca_mensuel"], color="#3498db", alpha=0.6, label="CA mensuel")
        ax1.plot(range(len(df)), df["ma3_mensuel"], color="#e74c3c",
                 linewidth=2, label="Moyenne mobile 3 mois")
        ax1.set_ylabel("CA (USD)")
        ax1.legend()
        ax1.set_title("CA mensuel")
        ax1.set_xticks(range(len(df)))
        ax1.set_xticklabels(df["mois"], rotation=45, ha="right", fontsize=7)

        # Axe 2 : Croissance mensuelle
        ax2 = axes[1]
        couleurs = ["#2ecc71" if v > 0 else "#e74c3c"
                    for v in df["croissance_pct"].fillna(0)]
        ax2.bar(range(len(df)), df["croissance_pct"].fillna(0),
                color=couleurs, alpha=0.8)
        ax2.axhline(0, color="black", linewidth=0.8)
        ax2.set_ylabel("Croissance (%)")
        ax2.set_title("Croissance mensuelle (%)")
        ax2.set_xticks(range(len(df)))
        ax2.set_xticklabels(df["mois"], rotation=45, ha="right", fontsize=7)

        plt.tight_layout()
        return self._sauvegarder("evolution_ca")

    def carte_pays(self) -> Path:
        """
        Visualisation des revenus par pays (barres horizontales).
        """
        df = self.analyzer.analyse_geographique().head(15)

        fig, ax = plt.subplots(figsize=(10, 8))

        # Gradient de couleur selon le rang
        n = len(df)
        couleurs = plt.cm.Blues(np.linspace(0.4, 0.9, n))[::-1]

        bars = ax.barh(range(n), df["ca_total"], color=couleurs, edgecolor="white")

        # Annotations
        for bar, (_, row) in zip(bars, df.iterrows()):
            ax.text(bar.get_width() + 5, bar.get_y() + bar.get_height() / 2,
                    f"${row['ca_total']:,.0f} ({row['part_marche_pct']:.1f}%)",
                    va="center", fontsize=8)

        ax.set_yticks(range(n))
        ax.set_yticklabels(df["pays"])
        ax.set_xlabel("CA Total (USD)")
        ax.set_title("Top 15 Pays par Chiffre d'Affaires\n(avec part de marché)",
                     fontweight="bold")
        ax.spines[["top", "right"]].set_visible(False)

        plt.tight_layout()
        return self._sauvegarder("revenus_par_pays")

    def genres_pie_et_bar(self) -> Path:
        """
        Double graphique : camembert (top 5) + barres (tous les genres).
        """
        df = self.analyzer.analyse_genres()

        fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(14, 6))
        fig.suptitle("Analyse des Genres Musicaux", fontweight="bold")

        # Camembert (top 5 + "Autres")
        top5 = df.head(5)
        autres = df.iloc[5:]["revenus_usd"].sum()
        valeurs = list(top5["revenus_usd"]) + [autres]
        labels  = list(top5["genre"]) + ["Autres"]
        ax1.pie(valeurs, labels=labels, autopct="%1.1f%%",
                startangle=90, pctdistance=0.8)
        ax1.set_title("Répartition des Revenus (Top 5)")

        # Barres horizontales
        df_sorted = df.sort_values("revenus_usd")
        ax2.barh(range(len(df_sorted)), df_sorted["revenus_usd"],
                 color="#3498db", alpha=0.8)
        ax2.set_yticks(range(len(df_sorted)))
        ax2.set_yticklabels(df_sorted["genre"], fontsize=8)
        ax2.set_xlabel("Revenus USD")
        ax2.set_title("Tous les Genres")
        ax2.spines[["top", "right"]].set_visible(False)

        plt.tight_layout()
        return self._sauvegarder("genres_analyse")

    def heatmap_ventes_temporelle(self) -> Path:
        """
        Heatmap mois × année pour visualiser la saisonnalité.
        """
        query = """
            SELECT
                CAST(strftime('%Y', InvoiceDate) AS INTEGER)  AS annee,
                CAST(strftime('%m', InvoiceDate) AS INTEGER)  AS mois,
                ROUND(SUM(Total), 2)                          AS ca
            FROM Invoice
            GROUP BY annee, mois
            ORDER BY annee, mois
        """
        with __import__('src.db_connection', fromlist=['DatabaseConnection']).DatabaseConnection() as conn:
            df = pd.read_sql(query, conn)

        # Pivot : années en colonnes, mois en lignes
        pivot = df.pivot(index="mois", columns="annee", values="ca")
        pivot.index = ["Jan","Fév","Mar","Avr","Mai","Juin",
                       "Juil","Août","Sep","Oct","Nov","Déc"][:len(pivot)]

        fig, ax = plt.subplots(figsize=(10, 6))
        sns.heatmap(pivot, ax=ax, annot=True, fmt=".0f",
                    cmap="YlOrRd", linewidths=0.5,
                    cbar_kws={"label": "CA (USD)"})
        ax.set_title("Saisonnalité des Ventes\n(CA par mois et année)",
                     fontweight="bold")
        ax.set_xlabel("Année")
        ax.set_ylabel("Mois")

        plt.tight_layout()
        return self._sauvegarder("heatmap_saisonnalite")
```

---

---

# PARTIE 7 : Cas Business Approfondis

---

## Questions business et requêtes SQL avancées

### Question 1 : Analyse de rétention (clients revenant)

```sql
WITH premier_achat AS (
    SELECT CustomerId, MIN(InvoiceDate) AS date_premier_achat
    FROM Invoice GROUP BY CustomerId
),
factures_avec_cohorte AS (
    SELECT
        i.CustomerId,
        i.InvoiceDate,
        pa.date_premier_achat,
        -- Mois de la cohorte (mois du premier achat)
        strftime('%Y-%m', pa.date_premier_achat) AS cohorte,
        -- Numéro du mois depuis l'entrée dans la cohorte
        CAST(
            (julianday(i.InvoiceDate) - julianday(pa.date_premier_achat))
            / 30.0
        AS INTEGER)                              AS mois_depuis_entree
    FROM Invoice i
        JOIN premier_achat pa ON i.CustomerId = pa.CustomerId
)
SELECT
    cohorte,
    mois_depuis_entree,
    COUNT(DISTINCT CustomerId) AS nb_clients_actifs
FROM factures_avec_cohorte
GROUP BY cohorte, mois_depuis_entree
ORDER BY cohorte, mois_depuis_entree;
```

### Question 2 : Recommandation de produits (clients similaires)

```sql
-- Trouver les pistes achetées par des clients similaires
-- (qui ont acheté les mêmes pistes qu'un client donné)
WITH pistes_client AS (
    -- Pistes achetées par le client cible (ici: CustomerId = 1)
    SELECT DISTINCT il.TrackId
    FROM Invoice i JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
    WHERE i.CustomerId = 1
),
clients_similaires AS (
    -- Clients ayant acheté au moins une piste identique
    SELECT DISTINCT i.CustomerId
    FROM Invoice i
        JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
    WHERE il.TrackId IN (SELECT TrackId FROM pistes_client)
      AND i.CustomerId != 1
),
pistes_recommandees AS (
    -- Pistes achetées par les clients similaires mais PAS par le client cible
    SELECT DISTINCT
        t.Name              AS piste,
        ar.Name             AS artiste,
        g.Name              AS genre,
        COUNT(DISTINCT i.CustomerId) AS nb_clients_qui_ont_achete
    FROM Invoice i
        JOIN InvoiceLine il ON i.InvoiceId = il.InvoiceId
        JOIN Track t        ON il.TrackId  = t.TrackId
        JOIN Album al       ON t.AlbumId   = al.AlbumId
        JOIN Artist ar      ON al.ArtistId = ar.ArtistId
        JOIN Genre g        ON t.GenreId   = g.GenreId
    WHERE i.CustomerId IN (SELECT CustomerId FROM clients_similaires)
      AND il.TrackId NOT IN (SELECT TrackId FROM pistes_client)
    GROUP BY t.TrackId, t.Name, ar.Name, g.Name
)
SELECT * FROM pistes_recommandees
ORDER BY nb_clients_qui_ont_achete DESC
LIMIT 10;
```

### Question 3 : Indice de fidélité client

```python
# Dans analysis.py : calcul du score de fidélité composite

def calculer_score_fidelite(self) -> pd.DataFrame:
    """
    Score de fidélité composite combinant :
    - Fréquence d'achat (F)
    - Montant total (M)
    - Récence (R)
    """
    query = """
        SELECT
            c.CustomerId,
            c.FirstName || ' ' || c.LastName    AS client,
            c.Country,
            COUNT(i.InvoiceId)                  AS frequence,
            ROUND(SUM(i.Total), 2)              AS montant_total,
            CAST(julianday('now') - julianday(MAX(i.InvoiceDate)) AS INTEGER) AS recence_jours
        FROM Customer c
            JOIN Invoice i ON c.CustomerId = i.CustomerId
        GROUP BY c.CustomerId, c.FirstName, c.LastName, c.Country
    """
    with DatabaseConnection() as conn:
        df = pd.read_sql(query, conn)

    # Normalisation Min-Max de chaque dimension
    for col in ["frequence", "montant_total"]:
        df[f"{col}_norm"] = (df[col] - df[col].min()) / (df[col].max() - df[col].min())

    # Pour la récence, inverser (récent = bon = score élevé)
    df["recence_norm"] = 1 - (df["recence_jours"] - df["recence_jours"].min()) / \
                             (df["recence_jours"].max() - df["recence_jours"].min())

    # Score composite pondéré (RFM)
    df["score_fidelite"] = (
        0.35 * df["recence_norm"] +
        0.35 * df["frequence_norm"] +
        0.30 * df["montant_total_norm"]
    ).round(3)

    # Segmentation
    df["segment_rfm"] = pd.qcut(
        df["score_fidelite"],
        q=4,
        labels=["À risque", "En développement", "Fidèle", "VIP"]
    )

    return df.sort_values("score_fidelite", ascending=False)
```

---

---

# PARTIE 8 : Projet Final — Rapport Complet

---

## Code : `main.py` final complet

```python
"""
main.py — DataInsight Pro SQL Edition — Version Finale

Exécute la pipeline complète d'analyse et génère le rapport HTML.

Usage :
    python main.py --action all           # Tout exécuter
    python main.py --action kpis          # KPIs seulement
    python main.py --action rapport       # Rapport HTML
"""

import argparse, sys, logging
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent))
from src.utils import configurer_logging, REPORTS_DIR
from src.db_connection import DatabaseConnection, lister_tables, compter_lignes
from src.data_cleaning import ChinookCleaner
from src.analysis import ChinookAnalyzer
from src.visualization import ChinookVisualizer

configurer_logging("INFO")
logger = logging.getLogger(__name__)


def pipeline_complete() -> dict:
    """Exécute la pipeline complète et retourne tous les résultats."""
    resultats = {}

    # 1. Vérification base
    with DatabaseConnection() as conn:
        tables = lister_tables(conn)
        logger.info(f"Base OK — {len(tables)} tables")

    # 2. Nettoyage
    cleaner = ChinookCleaner()
    resultats["clients_clean"]  = cleaner.nettoyer_clients()
    resultats["factures_clean"] = cleaner.nettoyer_factures()
    resultats["rapport_clean"]  = cleaner.rapport_nettoyage()

    # 3. Analyses
    analyzer = ChinookAnalyzer()
    resultats["kpis"]           = analyzer.kpis_globaux()
    resultats["geo"]            = analyzer.analyse_geographique()
    resultats["temporel"]       = analyzer.analyse_temporelle()
    resultats["clients"]        = analyzer.analyse_clients()
    resultats["genres"]         = analyzer.analyse_genres()
    resultats["fidelite"]       = analyzer.calculer_score_fidelite()

    # 4. Visualisations
    viz = ChinookVisualizer()
    resultats["fig_kpis"]      = viz.dashboard_kpis()
    resultats["fig_ca"]        = viz.evolution_ca()
    resultats["fig_pays"]      = viz.carte_pays()
    resultats["fig_genres"]    = viz.genres_pie_et_bar()
    resultats["fig_heatmap"]   = viz.heatmap_ventes_temporelle()

    # 5. Rapport HTML
    resultats["rapport_html"]  = generer_rapport_html(resultats)

    return resultats


def generer_rapport_html(resultats: dict) -> Path:
    """Génère le rapport HTML final avec tous les résultats."""
    import base64

    def img_base64(chemin: Path) -> str:
        if chemin and Path(chemin).exists():
            with open(chemin, "rb") as f:
                return f'<img src="data:image/png;base64,{base64.b64encode(f.read()).decode()}" style="max-width:100%;border-radius:8px;margin:10px 0"/>'
        return "<p><em>Image non disponible</em></p>"

    kpis = resultats.get("kpis", {})

    html = f"""<!DOCTYPE html>
<html lang="fr">
<head>
  <meta charset="UTF-8"/>
  <title>DataInsight Pro SQL — Rapport Chinook</title>
  <style>
    body{{font-family:'Segoe UI',sans-serif;max-width:1100px;margin:0 auto;padding:20px;background:#f8f9fa;}}
    header{{background:linear-gradient(135deg,#2c3e50,#3498db);color:#fff;padding:30px;border-radius:10px;margin-bottom:24px;}}
    h1{{margin:0;font-size:1.8em;}} .subtitle{{opacity:.8;}}
    .kpi-grid{{display:grid;grid-template-columns:repeat(3,1fr);gap:14px;margin:20px 0;}}
    .kpi{{background:#fff;border-radius:8px;padding:16px;text-align:center;box-shadow:0 2px 6px rgba(0,0,0,.08);}}
    .kpi-val{{font-size:1.6em;font-weight:700;color:#3498db;}}
    .kpi-lbl{{color:#666;font-size:.85em;margin-top:4px;}}
    section{{background:#fff;border-radius:10px;padding:24px;margin-bottom:20px;box-shadow:0 2px 6px rgba(0,0,0,.06);}}
    h2{{color:#2c3e50;border-bottom:2px solid #3498db;padding-bottom:8px;}}
    table{{width:100%;border-collapse:collapse;font-size:.9em;}}
    th{{background:#2c3e50;color:#fff;padding:8px 12px;text-align:left;}}
    td{{padding:7px 12px;border-bottom:1px solid #eee;}}
    tr:nth-child(even){{background:#f8f9fa;}}
    footer{{text-align:center;color:#888;padding:20px;font-size:.82em;}}
  </style>
</head>
<body>

<header>
  <h1>[GRAPHIQUE] DataInsight Pro — SQL Edition</h1>
  <div class="subtitle">Analyse Chinook Music Store | Base SQLite Réelle</div>
</header>

<section>
  <h2>[OBJECTIF] Indicateurs Clés</h2>
  <div class="kpi-grid">
    <div class="kpi"><div class="kpi-val">${kpis.get('ca_total',0):,.2f}</div><div class="kpi-lbl">[ARGENT] CA Total</div></div>
    <div class="kpi"><div class="kpi-val">{kpis.get('nb_factures',0):,}</div><div class="kpi-lbl">[FICHIER] Factures</div></div>
    <div class="kpi"><div class="kpi-val">${kpis.get('panier_moyen',0):.2f}</div><div class="kpi-lbl">[SHOPPING_TROLLEY] Panier Moyen</div></div>
    <div class="kpi"><div class="kpi-val">{kpis.get('nb_clients',0)}</div><div class="kpi-lbl">[UTILISATEUR] Clients</div></div>
    <div class="kpi"><div class="kpi-val">{kpis.get('nb_pistes_vendues',0):,}</div><div class="kpi-lbl">[SON] Pistes Vendues</div></div>
    <div class="kpi"><div class="kpi-val">{kpis.get('nb_pays',0)}</div><div class="kpi-lbl">[MONDE] Pays</div></div>
  </div>
  {img_base64(resultats.get('fig_kpis'))}
</section>

<section>
  <h2>[HAUSSE] Évolution du CA</h2>
  {img_base64(resultats.get('fig_ca'))}
</section>

<section>
  <h2>[MONDE] Analyse Géographique</h2>
  {img_base64(resultats.get('fig_pays'))}
</section>

<section>
  <h2>[SON] Genres Musicaux</h2>
  {img_base64(resultats.get('fig_genres'))}
</section>

<section>
  <h2>[CALENDRIER] Saisonnalité</h2>
  {img_base64(resultats.get('fig_heatmap'))}
</section>

<footer>DataInsight Pro SQL Edition | Base : <a href="https://github.com/lerocha/chinook-database">Chinook Database</a></footer>
</body></html>"""

    REPORTS_DIR.mkdir(parents=True, exist_ok=True)
    chemin = REPORTS_DIR / "final_report.html"
    with open(chemin, "w", encoding="utf-8") as f:
        f.write(html)
    logger.info(f"Rapport HTML généré : {chemin}")
    return chemin


def main():
    parser = argparse.ArgumentParser(description="DataInsight Pro SQL")
    parser.add_argument("--action", choices=["all","kpis","clean","viz","rapport"],
                        default="all")
    args = parser.parse_args()

    if args.action == "all":
        resultats = pipeline_complete()
        print(f"\n[OK] Rapport généré : {resultats['rapport_html']}")
    elif args.action == "kpis":
        analyzer = ChinookAnalyzer()
        kpis = analyzer.kpis_globaux()
        for k, v in kpis.items():
            print(f"  {k:<25} : {v}")

if __name__ == "__main__":
    main()
```

---

## Exercices Finaux

### [ROUGE] Exercice Final 1 — Analyse ABC des clients

Implémente une analyse ABC (Pareto 80/20) des clients :
- Classe A : 20% des clients qui génèrent 80% du CA
- Classe B : 30% suivants (10% du CA)
- Classe C : 50% restants (10% du CA)

Affiche la distribution et crée une visualisation.

### [ROUGE] Exercice Final 2 — Dashboard SQL

Crée une requête SQL unique (en utilisant des CTEs) qui retourne
en une seule exécution :
- Top 5 pays par CA
- Top 5 genres par revenus
- Top 5 clients par LTV
- KPIs globaux

Utilise UNION ALL pour combiner les résultats.

### Corrigé Exercice Final 1 — Analyse ABC

```sql
WITH ltv_clients AS (
    SELECT
        c.CustomerId,
        c.FirstName || ' ' || c.LastName    AS client,
        ROUND(SUM(i.Total), 2)              AS ltv
    FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId
    GROUP BY c.CustomerId, c.FirstName, c.LastName
),
avec_cumul AS (
    SELECT
        client, ltv,
        SUM(ltv) OVER (ORDER BY ltv DESC ROWS UNBOUNDED PRECEDING)  AS ltv_cumule,
        SUM(ltv) OVER ()                                             AS ltv_total
    FROM ltv_clients
),
avec_abc AS (
    SELECT
        client, ltv, ltv_cumule,
        ROUND(ltv_cumule * 100.0 / ltv_total, 1)    AS pct_cumule,
        CASE
            WHEN ltv_cumule / ltv_total <= 0.80 THEN 'A (Top 80%)'
            WHEN ltv_cumule / ltv_total <= 0.90 THEN 'B (80-90%)'
            ELSE                                     'C (Derniers 10%)'
        END                                         AS classe_abc
    FROM avec_cumul
)
SELECT
    classe_abc,
    COUNT(*)                    AS nb_clients,
    ROUND(SUM(ltv), 2)          AS ca_total,
    ROUND(AVG(ltv), 2)          AS ltv_moyen
FROM avec_abc
GROUP BY classe_abc
ORDER BY ca_total DESC;
```

---

## Récapitulatif Final — DataInsight Pro SQL Edition

### Compétences acquises sur les 8 parties

| Compétence | Partie |
|-----------|--------|
| Architecture projet Python | 1 |
| Connexion SQLite / Context Manager | 1 |
| Schéma relationnel (PK, FK, 1:N, N:N) | 1 |
| SELECT complet (WHERE, GROUP BY, HAVING, ORDER BY) | 2 |
| Fonctions SQL (texte, date, numériques) | 2 |
| CASE WHEN, sous-requêtes, CTEs | 2 |
| INNER/LEFT/FULL JOIN, auto-jointures | 3 |
| Window Functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) | 3 |
| EXISTS / NOT EXISTS | 3 |
| Audit qualité SQL + nettoyage Python | 4 |
| EDA complète (stats, distribution, outliers) | 5 |
| Visualisations Matplotlib/Seaborn | 6 |
| Saisonnalité, heatmaps temporelles | 6 |
| Analyse de cohortes, LTV, RFM | 7 |
| Recommandation de produits (SQL) | 7 |
| Pipeline complète + rapport HTML | 8 |

### Commandes pour lancer le projet

```bash
# Installation
pip install -r requirements.txt --break-system-packages

# Téléchargement de la base
python -c "
import urllib.request, pathlib
pathlib.Path('data').mkdir(exist_ok=True)
url = 'https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite'
urllib.request.urlretrieve(url, 'data/chinook.db')
print('Base téléchargée !')
"

# Vérification
python test_setup.py

# Pipeline complète
python main.py --action all

# Ouvrir le rapport
open reports/final_report.html  # Mac
start reports/final_report.html  # Windows
```

---

*DataInsight Pro SQL Edition — Parties 3-8 | Base : Chinook Music Database (lerocha/chinook-database)*